package gentle.service.impl;

import com.aliyun.oss.OSSClient;
import com.aliyun.oss.model.CopyObjectResult;
import com.aliyun.oss.model.OSSObject;
import com.baomidou.mybatisplus.plugins.Page;
import gentle.base.StringUtil;
import gentle.config.MemberEnumConfig;
import gentle.entity.*;
import gentle.exception.BizExceptionEnum;
import gentle.exception.BussinessException;
import gentle.exception.DateTimeKit;
import gentle.mapper.DoctorLookedDao;
import gentle.service.IDoctorDetailsService;
import gentle.service.IDoctorLookedService;
import gentle.util.AliyunOssUtil;
import gentle.util.CommUtils;
import gentle.util.DateUtil;
import gentle.util.ToolUtil;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import gentle.base.SqlMapper;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
@Transactional
public class DoctorLookedImpl implements IDoctorLookedService {
    @Autowired
    private SqlMapper sqlMapper;
    @Autowired
    private DoctorLookedDao dao;
    @Autowired
    IDoctorDetailsService service;
    @Autowired
    AliyunOssUtil aliyunOssUtil;

    public List<DoctorLookedInfo> selectPageListByQueryGetMap(Page<DoctorLookedInfo> page, SearchDoctorLookedInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArrivalTime())) {
            params.put("arrivalTime", searchInfo.getArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            params.put("startTime", searchInfo.getStartTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerSex())) {
            params.put("customerSex", searchInfo.getCustomerSex());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerAgeStart())) {
            params.put("customerAgeStart", searchInfo.getCustomerAgeStart());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerAgeEnd())) {
            params.put("customerAgeEnd", searchInfo.getCustomerAgeEnd());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        }
        //String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableHead = searchInfo.getAbbreviation();
        params.put("tableName1", tableHead+"customer_info");
        params.put("tableName", tableHead+"deal_info");
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryPage(page, params);
    }

    public int getQueryPagesCount(Page<DoctorLookedInfo> page, SearchDoctorLookedInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArrivalTime())) {
            params.put("arrivalTime", searchInfo.getArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            params.put("startTime", searchInfo.getStartTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerSex())) {
            params.put("customerSex", searchInfo.getCustomerSex());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerAgeStart())) {
            params.put("customerAgeStart", searchInfo.getCustomerAgeStart());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerAgeEnd())) {
            params.put("customerAgeEnd", searchInfo.getCustomerAgeEnd());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        }
        String tableHead = searchInfo.getAbbreviation();
        params.put("tableName1", tableHead+"customer_info");
        params.put("tableName", tableHead+"deal_info");
        return dao.getQueryPagesInfoCount(page, params);
    }

    public List<DoctorLookedInfo> selectDoctorGetMap(Page<DoctorLookedInfo> page, SearchDoctorLookedInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getOperationTime())) {
            params.put("operationTime", searchInfo.getOperationTime());
        }
        if ("1".equals(searchInfo.getUserName())) {
            params.put("operationPeople", "");
        } else {
            params.put("operationPeople", searchInfo.getUserName());
        }
        String tableHead = searchInfo.getAbbreviation();
        params.put("tableName1", tableHead+"customer_info");
        params.put("store", searchInfo.getOutpatientName());
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryDoctorPages(page, params);
    }

    public int getQueryPagesDoctorCount(Page<DoctorLookedInfo> page, SearchDoctorLookedInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getOperationTime())) {
            params.put("operationTime", searchInfo.getOperationTime());
        }
        if ("1".equals(searchInfo.getUserName())) {
            params.put("operationPeople", "");
        } else {
            params.put("operationPeople", searchInfo.getUserName());
        }
        String tableHead = searchInfo.getAbbreviation();
        params.put("tableName1", tableHead+"customer_info");
        params.put("store", searchInfo.getOutpatientName());
        return dao.getQueryPagesDoctorCount(page, params);
    }

    public List<DoctorLookedInfo> selectPageListByDoctorRevisit(Page<DoctorLookedInfo> page, SearchDoctorLookedInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getRevisitStatus())) {
            params.put("revisitStatus", searchInfo.getRevisitStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartRevisitTime())) {
            params.put("startRevisitTime", searchInfo.getStartRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndRevisitTime())) {
            params.put("endRevisitTime", searchInfo.getEndRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartCreateTime())) {
            params.put("startCreateTime", searchInfo.getStartCreateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndCreateTime())) {
            params.put("endCreateTime", searchInfo.getEndCreateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCreateName())) {
            params.put("userName", searchInfo.getCreateName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getRevisit())) {
            params.put("revisit", searchInfo.getRevisit());
        }
        String tableHead = searchInfo.getAbbreviation();
        params.put("tableName", tableHead+"customer_info");
        params.put("outpatientName", searchInfo.getOutpatientName());
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryPages(page, params);
    }

    public int getQueryPagesRevisitCount(Page<DoctorLookedInfo> page, SearchDoctorLookedInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getRevisitStatus())) {
            params.put("revisitStatus", searchInfo.getRevisitStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartRevisitTime())) {
            params.put("startRevisitTime", searchInfo.getStartRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndRevisitTime())) {
            params.put("endRevisitTime", searchInfo.getEndRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartCreateTime())) {
            params.put("startCreateTime", searchInfo.getStartCreateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndCreateTime())) {
            params.put("endCreateTime", searchInfo.getEndCreateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCreateName())) {
            params.put("createName", searchInfo.getCreateName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getRevisit())) {
            params.put("revisit", searchInfo.getRevisit());
        }
        String tableHead = searchInfo.getAbbreviation();
        params.put("tableName", tableHead+"customer_info");
        params.put("outpatientName", searchInfo.getOutpatientName());
        return dao.getQueryPagesRevisitCount(page, params);
    }

    public List<ConsultantInfo> getDataList(String timeSlot){
        String startTime = timeSlot.split(",")[0].substring(0, 10);
        String endTime = timeSlot.split(",")[1].substring(0, 10);
        String startTime2 = timeSlot.split(",")[0];
        String endTime2 = timeSlot.split(",")[1];
        String abbreviation = timeSlot.split(",")[2];
        String sql1 = "";
        if (startTime.equals(endTime)) {
            sql1 += "left(t.start_time,10) = '" + startTime + "'";
        } else {
            sql1 += "t.start_time >= '" + startTime2 + "' and t.end_time <= '" + endTime2 + "'";
        }
        //根据角色用户查询所有医生
        String sql = " select * from (select t1.id as id,t.customer_name as customerName,customer_status as customerStatus,t.check_status as checkStatus,t.customer_age as customerAge,t.customer_phone as customerPhone,t.medical_record_number as medicalRecordNumber," +
                " ( " +
                " CASE t.customer_sex " +
                " WHEN 1 THEN " +
                "     '男' " +
                " WHEN 2 THEN " +
                "     '女' " +
                " ELSE " +
                "     '未知' " +
                " END " +
                "    ) as sex ,t.customer_sex_head as customerSexHead,t.archiving as archiving ,t.reservations_id as reservationsId,t.yellow_number as yellowNumber," +
                "t.treatment_unit as treatmentUnit,t.account_arrears as accountArrears," +
                "(select count(id) from "+abbreviation+"customer_info b where t.reservations_id=b.reservations_id and left(start_time,10)=left(now(),10)) as todaySum," +
                "(select count(id) from "+abbreviation+"customer_info b where t.reservations_id=b.reservations_id and left(start_time,10)>left(now(),10)) as aftersSum," +
                "(select count(id) from "+abbreviation+"customer_info b where t.reservations_id=b.reservations_id and left(start_time,10)<left(now(),10)) as beforeSum," +
                "t.arrival_status as arrivalStatus,t.arrival_store as arrivalStore ,t.birthday as birthday ," +
                "left(t.create_appointment_time,19) as createAppointmentTime,t.remarks as remarks,t.remarks_reservation as remarksReservation, t.field_tc as fieldTc,t.first_visit_date as firstVisitDate," +
                "t.appointment_pay_service as appointmentPayService ,t.start_time as startTime, t.end_time as endTime,t.doctor,t.id as customerId from "+abbreviation+"customer_info t " +
                "left join user t1 on t.doctor=t1.account where " + sql1 + " and t.arrival_status!=8 order by t.create_appointment_time) t2 ";
        List<ConsultantInfo> DataList = sqlMapper.selectList(sql, ConsultantInfo.class);
        return DataList;
    }

    public List<ConsultantdInfo> getListed(String timeSlot){
        String startTime = timeSlot.split(",")[0].substring(0, 10);
        String endTime = timeSlot.split(",")[1].substring(0, 10);
        String dentistRoleId = timeSlot.split(",")[2];
        String doctorRoleId = timeSlot.split(",")[3];
        String deptId = timeSlot.split(",")[4];
        String publicDoctorRoleId = timeSlot.split(",")[5];
        String outpatientName = timeSlot.split(",")[6];
        String sql = "select id,doctor from (" +
                " (select a.id as id,a.account as doctor,a.roleid from user a where (a.roleid=" + dentistRoleId + "" +
                " or a.roleid=" + doctorRoleId + ") and a.deptid like CONCAT('%'," + deptId + ",',','%') and a.status=1 and a.id" +
                " not in(select b.doctor_id from doctor_scheduling b where " +
                " b.the_date>='" + startTime + "' and b.the_date<='" + endTime + "' and b.status=2 and b.outpatient_name='" + outpatientName + "')) " +
                " union all (select " + publicDoctorRoleId + " as id,'未指定医生' as doctor,61 from user d limit 0,1)" +
                " ) t  order by roleid,id;";
        List<ConsultantdInfo> todayDocViewsList = sqlMapper.selectList(sql, ConsultantdInfo.class);
        return todayDocViewsList;
    }

    public List<ConsultantsInfo> consultRestDoctor(String timeSlot){
        String startTime = timeSlot.split(",")[0].substring(0, 10);
        String endTime = timeSlot.split(",")[1].substring(0, 10);
        String abbreviation = timeSlot.split(",")[7];
        String outpatientName = timeSlot.split(",")[6];
        String sql = "select b.doctor_id as id,b.doctor_name as doctor,count(1) as countNum from doctor_scheduling b " +
                " left join " + abbreviation + "customer_info a on a.doctor=b.doctor_name " +
                " where b.the_date >='" + startTime + "' and b.the_date <='" + endTime + "' and " +
                " left(a.start_time,10)>='" + startTime + "' and left(a.end_time,10)<='" + endTime + "' and a.arrival_status!='8'" +
                " and b.status=2 and b.outpatient_name='" + outpatientName + "' group by b.doctor_id";
        List<ConsultantsInfo> todayRestDoctorList = sqlMapper.selectList(sql, ConsultantsInfo.class);
        return todayRestDoctorList;
    }

    public CustomerInfo selectOneById(String customerId,String abbreviation ) {
        String sql = "select * from " + abbreviation+"customer_info  where id='" + customerId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        return customerInfo;
    }

    public CustomerInfo selectOneByReservationId(String reservationsId, String abbreviation) {
        String sql = "select * from " + abbreviation+"customer_info where reservations_id='" + reservationsId + "' limit 1";
        CustomerInfo cinfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        return cinfo;
    }

    public List<ConsultantsoInfo> getListedDoctor(String timeSlot){
        String sql = "select 361 as id,'未指定医生' as doctor,61;";
        List<ConsultantsoInfo> todayDocViewsList = sqlMapper.selectList(sql, ConsultantsoInfo.class);
        return todayDocViewsList;
    }

    public List<ConsultantdInfo> getJysListed(String timeSlot){
        String startTime = timeSlot.split(",")[0].substring(0, 10);
        String endTime = timeSlot.split(",")[1].substring(0, 10);
        String dentistRoleId = timeSlot.split(",")[2];
        String deptId = timeSlot.split(",")[4];
        String publicDoctorRoleId = timeSlot.split(",")[5];
        String outpatientName = timeSlot.split(",")[6];
        String sql = "select a.id as id,a.account as doctor from user a where a.roleid=" + dentistRoleId + " " +
                " and a.deptid like CONCAT('%'," + deptId + ",',','%') and a.status=1 and a.id" +
                " not in(select b.doctor_id from doctor_scheduling b where " +
                " b.the_date>='" + startTime + "' and b.the_date<='" + endTime + "' and b.status=2 and b.outpatient_name='" + outpatientName + "')" +
                " union all (select " + publicDoctorRoleId + " as id,'未指定医生' as doctor from user d limit 0,1)";
        List<ConsultantdInfo> todayJysList = sqlMapper.selectList(sql, ConsultantdInfo.class);
        return todayJysList;
    }

    public double totalMoney(String reach, String payServiceName, String startTime, String endTime, String abbreviation, String userAccount, String areaAbbreviation) {
        String sqltj = "";
        if (StringUtil.isNotEmpty(startTime)) {
            sqltj += " and  t.operation_time >= '" + startTime + "' ";
        }
        if (StringUtil.isNotEmpty(endTime)) {
            sqltj += " and  t.operation_time <= '" + endTime + "' ";
        }
        if (StringUtil.isNotEmpty(payServiceName)) {
            sqltj += " and  t1.pay_service_name like CONCAT('%" + payServiceName + "%') ";
        }
        String sql2 = "select b.id as roleid from user a left join role b on a.roleId=b.id where a.account='" + userAccount + "' limit 1";
        User user = sqlMapper.selectOne(sql2, User.class);
        if (user != null) {
            if ("1".equals(user.getRoleid()) || "1,".equals(user.getRoleid().substring(0, 2))) {
                if (StringUtil.isNotEmpty(reach)) {
                    sqltj += "  and  t.doctor = '" + reach + "' ";
                } else {
                    sqltj += " ";
                }
            } else {
                sqltj += " and t.doctor = '" + userAccount + "' ";
            }
        }
        String sql = "select sum(t4.serviceActualPay) serviceActualPay from( select sum(t3.serviceActualPay) as serviceActualPay, sum(t3.foldingPay) as foldingPay,t3.id,t3.id2,t3.doctor, t3.payServiceName,sum(t3.payServiceNum) as payServiceNum " +
                " from  (select sum(t.service_actual_pay) as serviceActualPay, t.folding_pay as foldingPay ,t.id as id, t.id2 as id2,t.doctor as doctor, " +
                " t1.pay_service_name as payServiceName,t.pay_service_num as payServiceNum,t.pay_service_id as payServiceId from " + abbreviation+"deal_info t " +
                " left join "+ areaAbbreviation+"pay_service_info  t1 on t.pay_service_id=t1.id " +
                " left join " + abbreviation+"customer_info t2 on t.customer_id=t2.id where t.deal_status='已结账' " + sqltj +
                " group by t.id2 order by t.operation_time) t3 group by t3.payServiceId ) t4 limit 1";
        PayServicedInfo searchInfo = sqlMapper.selectOne(sql, PayServicedInfo.class);
        return searchInfo.getServiceActualPay();
    }

    public List<DoctorLookedInfo> alltotal(String reach,String revisitStatus,String startRevisitTime,String endRevisitTime,String revisit,String createName,
                                           String startCreateTime,String endCreateTime, String abbreviation, String outpatientName) {
        String contitionInfo = "";
        contitionInfo +="and t.outpatient_name = '"+outpatientName+"'";
        if (StringUtils.isNotBlank(startRevisitTime)) {
            contitionInfo += " and t.revisit_time >='"+startRevisitTime+"' ";
        }
        if (StringUtils.isNotBlank(endRevisitTime)) {
            contitionInfo += " and t.revisit_time <='"+endRevisitTime+"' ";
        }
        if (StringUtils.isNotBlank(startCreateTime)) {
            contitionInfo += " and t.create_time >='"+startCreateTime+"' ";
        }
        if (StringUtils.isNotBlank(endCreateTime)) {
            contitionInfo += " and t.create_time <='"+endCreateTime+"' ";
        }
        if (StringUtils.isNotBlank(revisit)) {
            contitionInfo += " and t.revisit='" + revisit + "' ";
        }
        if (StringUtils.isNotBlank(createName)) {
            contitionInfo += " and t.create_name='" + createName + "' ";
        }
        if (StringUtils.isNotBlank(reach)) {
            contitionInfo += "   and (t1.customer_name like CONCAT('%','"+reach+"','%') or t1.customer_phone like CONCAT('%','"+reach+"','%') or " +
                    "            t1.wechat_number like CONCAT('%','"+reach+"','%') or t1.medical_record_number like CONCAT('%','"+reach+"','%') " +
                    "            or t1.customer_phone2 like CONCAT('%','"+reach+"','%')  or t1.szm like CONCAT('%','"+reach+"','%') or t.doctor like CONCAT('%','"+reach+"','%')) ";
        }
        contitionInfo += " group by t.id";
        String sql = "select count(*) as countNum from (select t.id from return_doctor_visit_info t,"+abbreviation+"customer_info t1 where t.customer_id = t1.id " + contitionInfo+") t "+
                " union all select count(*) as countNum from (select t.id from  return_doctor_visit_info t , "+abbreviation+"customer_info t1 where t.customer_id = t1.id and t.revisit_status ='1' " + contitionInfo+") t"+

                " union all  select count(*) as countNum from (select t.id from  return_doctor_visit_info t , "+abbreviation+"customer_info t1 where t.customer_id = t1.id and t.revisit_status ='2' " + contitionInfo+") t"+

                " union all  select count(*) as countNum from (select t.id from  return_doctor_visit_info t , "+abbreviation+"customer_info t1 where t.customer_id = t1.id and t.revisit_status ='3' " + contitionInfo+") t"+

                " union all  select count(*) as countNum from (select t.id from  return_doctor_visit_info t , "+abbreviation+"customer_info t1 where t.customer_id = t1.id and t.revisit_status ='4' " + contitionInfo+") t";
        List<DoctorLookedInfo> tongJiInfoList1 = sqlMapper.selectList(sql, DoctorLookedInfo.class);
        return tongJiInfoList1;
    }

    public List<DoctorLookedInfo> selectDoctorChargePrice(Page<DoctorLookedInfo> page, SearchDoctorLookedInfo searchInfo,String abbreviation) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            params.put("startTime", searchInfo.getStartTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndTime())) {
            params.put("endTime", searchInfo.getEndTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCheckStatus())) {
            params.put("checkStatus", searchInfo.getCheckStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getExecuteStatus())) {
            params.put("executeStatus", searchInfo.getExecuteStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        }
        String tableName1 = abbreviation + "deal_info";
        String tableName = abbreviation + "customer_info";
        params.put("tableName1", tableName1);
        params.put("tableName", tableName);
        return dao.getQueryPageDoctorChargePrice(page, params);
    }

    public List<DoctorLookedInfo> selectChargeSchedule(Page<DoctorLookedInfo> page, SearchDoctorLookedInfo searchInfo,String userAccount,String abbreviation,String areaAbbreviation) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            params.put("startTime", searchInfo.getStartTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndTime())) {
            params.put("endTime", searchInfo.getEndTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getPayServiceName())) {
            params.put("payServiceName", searchInfo.getPayServiceName());
        }
        String sql = "select b.id as roleid from user a left join role b on a.roleId=b.id where a.account='" + userAccount + "'";
        User user = sqlMapper.selectOne(sql, User.class);
        if (user != null) {
            if ("1".equals(user.getRoleid()) || "1,".equals(user.getRoleid().substring(0, 2))) {
                if (StringUtil.isNotEmpty(searchInfo.getReach())) {
                    params.put("doctor", searchInfo.getReach());
                } else {
                    params.put("doctor", "");
                }
            } else {
                params.put("doctor", userAccount);
            }
        }
        String tableName1 = abbreviation + "deal_info";
        String tableName = abbreviation + "customer_info";
        params.put("tableName1", tableName1);
        params.put("tableName", tableName);
        params.put("pay_service_info", areaAbbreviation+"pay_service_info");
        return dao.getQueryPageChargeSchedule(page, params);
    }

    public List<CaseInfo> getArchivings(String id) {
        String sql = "select * from  case_info t1 LEFT JOIN tooth_site t2 on t1.id=t2.case_id where t1.id='" + id + "'";
        List<CaseInfo> list = sqlMapper.selectList(sql, CaseInfo.class);
        return list;
    }

    public int deleteSfById(String id) {
        String sql = "delete from follow_template where id='" + id + "'";
        return sqlMapper.delete(sql);
    }

    public int deleteJzById(String id) {
        String sql = "delete from orthodontic_template where id='" + id + "'";
        return sqlMapper.delete(sql);
    }

    public int deleteSjById(String id) {
        String sql = "delete from design_scheme where id='" + id + "'";
        return sqlMapper.delete(sql);
    }

    public int deleteJgById(String id) {
        String sql = "delete from follow_results_template where id='" + id + "'";
        return sqlMapper.delete(sql);
    }

    public int updateRemarksReservationOk(String customerId, String remarksReservation, String userAccount, String userId, String abbreviation, String outpatientName) {
        String selsql = "select id, (case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中' " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话' " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName,a.customer_name,a.customer_phone,a.customer_status," +
                "a.reservations_id ,a.remarks_reservation from "+abbreviation+"customer_info a where id = '" + customerId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        String sql = "update "+abbreviation+"customer_info set remarks_reservation='" + remarksReservation + "'" +
                " where id='" + customerId + "'";
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userAccount + "','修改预约备注','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                "','修改预约备注：" + customerInfo.getRemarksReservation() + "->" + remarksReservation + "','" + customerId + "','" + customerInfo.getReservationsId() + "');";
        sqlMapper.update(sql);
        return sqlMapper.insert(sq11);
    }

    public int updAppointmentPayServiceOK(String customerId, String projectData, String userAccount, String userId, String abbreviation, String outpatientName) {
        String selsql = "select id, (case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中' " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话' " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName,a.customer_name,a.customer_phone,a.customer_status," +
                "a.reservations_id ,a.appointment_pay_service from " + abbreviation+"customer_info a where id = '" + customerId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        String sql = "update "+ abbreviation+"customer_info  set appointment_pay_service='" + projectData + "'" +
                " where id='" + customerId + "'";
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userAccount + "','修改看诊项目','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                "','修改看诊项目：" + customerInfo.getAppointmentPayService() + "->" + projectData + "','" + customerId + "','" + customerInfo.getReservationsId() + "');";
        sqlMapper.update(sql);
        return sqlMapper.insert(sq11);
    }

    public List<CustomerReservationsUpdateLogInfo> getList(Page<CustomerReservationsUpdateLogInfo> page, SearchCustomerReservationsUpdateLogInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getSinglePerson())) {
            params.put("singlePerson", searchInfo.getSinglePerson());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerId())) {
            params.put("customerId", searchInfo.getCustomerId());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerName())) {
            params.put("customerName", searchInfo.getCustomerName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerPhone())) {
            params.put("customerPhone", searchInfo.getCustomerPhone());
        }
        if (StringUtil.isNotEmpty(searchInfo.getLogtype())) {
            params.put("logtype", searchInfo.getLogtype());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            params.put("startTime", searchInfo.getStartTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndTime())) {
            params.put("endTime", searchInfo.getEndTime());
        }
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getList(page, params);
    }

    public int getQueryPagesCount(Page<CustomerReservationsUpdateLogInfo> page, SearchCustomerReservationsUpdateLogInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getSinglePerson())) {
            params.put("singlePerson", searchInfo.getSinglePerson());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerId())) {
            params.put("customerId", searchInfo.getCustomerId());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerName())) {
            params.put("customerName", searchInfo.getCustomerName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerPhone())) {
            params.put("customerPhone", searchInfo.getCustomerPhone());
        }
        if (StringUtil.isNotEmpty(searchInfo.getLogtype())) {
            params.put("logtype", searchInfo.getLogtype());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            params.put("startTime", searchInfo.getStartTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndTime())) {
            params.put("endTime", searchInfo.getEndTime());
        }
        return dao.getQueryPagesCount(page, params);
    }

    public List<DoctorDailyLog> getDocotrDailyLogList(Page<DoctorDailyLog> page, SearchDoctorDailyLog searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReservationsId())) {
            params.put("reservationsId", searchInfo.getReservationsId());
        }
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getDailyLogList(page,params);
    }

    public int getDailyPageCount(Page<DoctorDailyLog> page, SearchDoctorDailyLog searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        if (StringUtil.isNotEmpty(searchInfo.getReservationsId())) {
            params.put("reservationsId", searchInfo.getReservationsId());
        }
        params.put("page", page);
        return dao.getDailyLogCount(page, params);
    }

    public int add_Oblogs(String oneClass, String twoClass, String userAccount) {
        String sq11 = "INSERT INTO ob_log (one_class,two_class,doctor) VALUES " +
                " ('" + oneClass + "','" + twoClass + "','" + userAccount + "');";
        return sqlMapper.insert(sq11);
    }

    public int addCaseContent(String caseName, String zhushu, String nowMedicalHistory, String beforeMedicalHistory, String mouthCheck, String auxiliaryCheck, String remedyPlan, String diagnose, String dispose, String doctorAdvice, String oblogid, String userAccount) {
        String sq11 = "INSERT INTO cases_content (oblogid,case_name, zhushu,now_medical_history,before_medical_history,mouth_check, " +
                "auxiliary_check,diagnose,remedy_plan,dispose,doctor_advice,doctor) VALUES " +
                " ('" + oblogid + "','" + caseName + "','" + zhushu + "', '" + nowMedicalHistory + "', '" + beforeMedicalHistory + "', '" + mouthCheck + "','" + auxiliaryCheck +
                "','" + diagnose + "','" + remedyPlan + "','" + dispose + "' ,'" + doctorAdvice + "','" + userAccount + "');";
        return sqlMapper.insert(sq11);
    }

    public CaseInfo getCaseContent(String id) {
        String sq11 = "select * from cases_content a left join ob_log b on a.oblogid=b.id " +
                "where a.id='" + id + "' limit 1";
        CaseInfo caseInfo = sqlMapper.selectOne(sq11, CaseInfo.class);
        return caseInfo;
    }

    public int deleteCaseContent(String id, String userAccount) {
        String role = "select a.name from role a left join user b on a.id=b.roleid where b.account='" + userAccount + "' limit 1";
        Role role1 = sqlMapper.selectOne(role, Role.class);
        String sq11;
        if ("超级管理员".equals(role1.getName())) {
            sq11 = "delete from cases_content  where id='" + id + "' ";
        } else {
            sq11 = "delete from cases_content   where id='" + id + "' and doctor='" + userAccount + "'";
        }
        return sqlMapper.delete(sq11);
    }

    public int updateNumber(String customerId, String yellowNumber, String abbreviation, String userAccount, String userId) {
        String sql1 = "select t.customer_name as customerName,t.customer_phone as customerPhone,t.yellow_number as yellowNumber,t.archiving as archiving,t.id," +
                "t.reservations_id as reservationsId from " + abbreviation+"customer_info t where id='" + customerId + "' limit 1";
        CustomerInfo info = sqlMapper.selectOne(sql1, CustomerInfo.class);
        String sql = "update " + abbreviation+"customer_info  set yellow_number ='" + yellowNumber + "'" +
                " where reservations_id = '" + info.getReservationsId() + "'";

        String sq11 = "INSERT INTO yellow_number_update_log (logtype, userid,customer_name,customer_phone,single_person, createtime,succeed,classname) VALUES " +
                " ( '修改'," + userId + ", '" + info.getCustomerName() + "', '" + info.getCustomerPhone() + "', '" + userAccount + "','" + DateTimeKit.now() + "'," +
                "'成功','" + userAccount + " 把 " + info.getCustomerName() + " 的黄页编号:" + info.getYellowNumber() + " 修改为:" + yellowNumber + "');";
        sqlMapper.insert(sq11);
        return sqlMapper.update(sql);
    }

    public int addRecord( String customerId, String doctor, String diagnoseTime, String expenditureProject, String diagnoseDetail, String remarks, String abbreviation) {
        //改变详情表状态
        String sql = "update  return_doctor_visit_info  set revisit_status= '2'  where customer_id='" + customerId + "'";
        sqlMapper.update(sql);
        String sql1 = "select * from " + abbreviation+"customer_info where id= '" + customerId + "' limit 1";
        DoctorDiagnosisInfo info1 = sqlMapper.selectOne(sql1, DoctorDiagnosisInfo.class);
        String sql2 = "insert into  `doctor_diagnosis_info` (`customer_id`,`reservations_id`,`doctor`,`diagnose_detail`," +
                "`diagnose_time`,pay_service_name,remarks,outpatient_name) VALUES" +
                "('" + customerId + "','" + info1.getReservationsId() + "','" + doctor + "','" + diagnoseDetail
                + "','" + diagnoseTime + "','" + expenditureProject + "','" + remarks + "','" + info1.getArrivalStore()
                + "');";
        return sqlMapper.insert(sql2);
    }

    public List<PayServicedInfo> getDealInfoList(String operationTime, String reservationsId) {
        if (ToolUtil.isEmpty(operationTime)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' and status=1 ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        String rechargeTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' ";
                rechargeTables += "select * from " + head + "recharge_info where reservations_id='" + reservationsId + "' ";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' union ";
                rechargeTables += "select * from " + head + "recharge_info where reservations_id='" + reservationsId + "' union ";
            }
        }
        String sql = "select t1.id as id,t1.outpatient_name as outpatientName,left(t1.get_time,19) as getTime,t1.is_do as isDo,t1.deal_status as dealStatus," +
                "t1.doctor1 as doctor,t1.nurse1 as nurse,t1.order_discount as orderDiscount,t1.deductionAmount as deductionAmount,t1.remarks as remarks,t1.serviceActualPay as serviceActualPay," +
                "t2.recharge_people as rechargePeople,t2.checkout_method as checkoutMethod," +
                "t2.document_number as documentNumber,t1.tc_name as tcName,left(t1.operation_time,19) as operationTime from (select t.id2 as id,t.outpatient_name as outpatient_name," +
                "t.get_time as get_time,t.tc_name as tc_name,t.is_do as is_do,t.deal_status as deal_status,GROUP_CONCAT(DISTINCT t.doctor) AS doctor1,GROUP_CONCAT(DISTINCT t.nurse) AS nurse1,(sum(t.service_actual_pay)+sum(t.deposit_amount)) as serviceActualPay," +
                "t.order_discount as order_discount,sum(t.deduction_amount) as deductionAmount,t.remarks as remarks,t.operation_time as operation_time from (" + dealTables + ") t where t.operation_time='" + operationTime + "' and t.reservations_id " +
                "= '" + reservationsId + "' and t.deal_status='已结账' group by t.get_time) t1 left join " + "(select b.recharge_people as recharge_people," +
                "b.document_number as  document_number,group_concat(distinct b.checkout_method) as checkout_method, b.operation_time as operation_time from (" + rechargeTables + ") b " +
                "group by b.operation_time) t2 on t1.get_time = t2.operation_time order by t1.get_time desc";
        List<PayServicedInfo> payServiceList = sqlMapper.selectList(sql, PayServicedInfo.class);
        return payServiceList;
    }

    public List<IconInfo> getCurrentList(String num, String reservationsId) {
        if (ToolUtil.isEmpty(num)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select t1.id as id,t1.outpatient_name as outpatientName,t1.add_time as addTime,t1.add_name as addName,t1.customer_name as customerName," +
                "t1.remarks as remarks,t1.image_url as imageUrl,t1.status as status,t1.score as score,t1.cause as cause,t1.num as num from icon_infos t1  where t1.num='" + num + "' and t1.reservations_id " +
                "= '" + reservationsId + "'  order by t1.icon_name1 desc";
        List<IconInfo> currentList = sqlMapper.selectList(sql, IconInfo.class);
        return currentList;
    }

    public List<PayServicedInfo> getOpenBillLisHuajia(String operationTime, String reservationsId, String abbreviation,String areaAbbreviation) {
        if (StringUtil.isNotEmpty(operationTime) && operationTime.length() == 18) {//yyyy-mm-ddHH:mm:ss --- yyyy-mm-dd HH:mm:ss
            operationTime = operationTime.substring(0, 10) + " " + operationTime.substring(10);//中间加入空格
        }
        String sql3 = "SELECT  service_actual_pay  as serviceActualPay, arrears as  arrears, " +
                " t1.id AS id,t1.customer_id AS customerId,t1.reservations_id AS reservationsId,left(t1.get_time,19) AS getTime,t1.outpatient_name AS outpatientName, " +
                " t1.deal_status AS dealStatus, t1.tc_name AS tcName,t1.pay_service_num AS payServiceNum,t4.recharge_people as rechargePeople,t4.remarks as remarkss," +
                " LEFT(t1.operation_time,19) AS operationTime,t1.remarks AS remarks, t1.folding_pay AS foldingPay,t1.doctor AS doctor," +
                "(case " +
                "  when t1.is_do=1 then '不做了'" +
                "  when t1.is_do=2 then '继续做'" +
                "  when t1.is_do=3 then '还欠款'" +
                "  when t1.is_do=4 then '退款'" +
                "  when t1.is_do=5 then '消费'" +
                "  when t1.is_do=6 then '作废'" +
                "  end " +
                "  ) isDoName," +
                " t2.department AS department,t2.first_level_project AS firstLevelProject,t2.pay_service_name AS payServiceName, t2.pay_service_price_one AS payServicePriceOne, " +
                " t2.pay_service_price_two AS payServicePriceTwo,t3.customer_name AS customerName," +
                " t3.medical_record_number AS medicalRecordNumber,t3.customer_phone AS customerPhone,t3.customer_age AS customerAge,t1.id2,t3.arrival_store AS arrivalStore,t1.tc_name AS fieldTc,t1.check_status AS checkStatus,t4.checkout_method as checkoutMethod " +
                " from " + abbreviation+"deal_info t1 left join " + areaAbbreviation+"pay_service_info t2 on t1.pay_service_id=t2.id left join " + abbreviation+"customer_info t3 on t1.customer_id=t3.id" +
                " left join " + abbreviation+"recharge_info t4 on t4.id2=t1.id where t1.operation_time='" + operationTime + "' " +
                " and t1.reservations_id='" + reservationsId + "' " +
                " and t1.deal_status='已结账'order by t1.get_time desc ";
        List<PayServicedInfo> openBillList3 = sqlMapper.selectList(sql3, PayServicedInfo.class);
        Double totleReceivable = 0.00;//总的项目总价,非折后
        Double totleServiceActualPays = 0.00;//总的实付金额（除去不做的）
        Double totleFoldingPay = 0.00;//总的折后价
        Double accountArrears = 0.00;
        for (PayServicedInfo info : openBillList3) {
            if (info != null) {
                if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                    if (info.getPayServiceNum() != null) {
                        totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
                    }
                    if (info.getFoldingPay() != null) {
                        totleFoldingPay += info.getFoldingPay();
                    }
                    if (info.getArrears() != null) {
                        accountArrears += info.getArrears();
                    }
                    if (info.getServiceActualPay() != null) {
                        totleServiceActualPays += info.getServiceActualPay();
                    }
                    info.setTotleReceivable(totleReceivable);
                    info.setTotleFoldingPay(totleFoldingPay);
                    info.setTotleServiceActualPay(totleServiceActualPays);
                    info.setAccountArrears(accountArrears);
                }
            }
        }
        return openBillList3;
    }

    public List<PayServicedInfo> getOpenBillListf(String operationTime, String reservationsId, String abbreviation,String areaAbbreviation) {
        if (StringUtil.isNotEmpty(operationTime) && operationTime.length() == 18) {//yyyy-mm-ddHH:mm:ss --- yyyy-mm-dd HH:mm:ss
            operationTime = operationTime.substring(0, 10) + " " + operationTime.substring(10);//中间加入空格deposit_amount
        }
        //医生划价数据,剩余可划扣实收=结账表实收-业绩表实收  业绩=结账表实收+(结账表积分金额  满足 结账方式如果= 付款方式表ID 中是否记录医生业绩表字段值为1  才加入计算)
        String sql3 = "SELECT IFNULL(t2.folding_pay,0) - IFNULL(t2.deduction_amount,0) AS totalPerformance," +
                " IFNULL(t2.serviceActualPay,0) - IFNULL(t3.serviceActualPay,0) + IFNULL(t2.deposit_amount, 0) as serviceActualPay," +
                "IFNULL(t2.deduction_amounts,0)-IFNULL(t3.deduction,0) AS deductionAmount," +
                "t2.deposit_amount1 as depositAmount1,t2.deduction_amount1 as deductionAmount1,t2.folding_pay AS foldingPay," +
                "t2.arrears as accountArrears,t2.id AS id,t2.customer_id AS customerId,t2.reservations_id AS reservationsId," +
                "t2.get_time AS getTime,t1.rechargeTime,t2.deal_status AS dealStatus,t2.pay_service_num AS payServiceNum," +
                "LEFT(t2.operation_time,19) AS operationTime,t2.remarks AS remarks,t2.doctor AS doctor,t2.left_up AS leftUp," +
                "t2.left_down AS leftDown,t2.right_up AS rightUp,t2.right_down AS rightDown,t2.execute_status as executeStatus," +
                "t2.id2,t4.pay_service_name as payServiceName from (SELECT SUM(w.service_actual_pay) AS serviceActualPay," +
                "sum(IF (c.charge_bill_is_docAchievement = 1,0,w.deduction_amount)) AS deduction_amount," +
                "sum(IF (c.charge_bill_is_docAchievement = 1,w.deduction_amount,0)) AS deduction_amounts," +
                "SUM(w.deposit_amount) AS deposit_amount," +
                "SUM(w.arrears) AS arrears,w.id2,w.id,w.customer_id,SUM(w.folding_pay+IF(w.is_do='4',arrears,0)) as folding_pay," +
                "w.deduction_amount as deduction_amount1,w.deposit_amount as deposit_amount1,w.get_time,w.deal_status," +
                "w.pay_service_num,w.reservations_id,w.operation_time,w.remarks,w.doctor,w.left_up,w.left_down,w.right_up," +
                "w.pay_service_id,w.right_down,w.execute_status FROM " + abbreviation+"deal_info w left join checkout_method_info c on c.id = w.deduction_type WHERE " +
                "w.reservations_id = '" + reservationsId + "' AND w.operation_time='" + operationTime + "' " +
                "AND w.deal_status = '已结账' GROUP BY w.id2) t2 left join (SELECT min(w.recharge_time) AS rechargeTime," +
                "w.id2,w.reservations_id FROM " + abbreviation+"recharge_info w WHERE w.reservations_id = '" + reservationsId + "' " +
                "GROUP BY id2) t1 on t2.id2=t1.id2 left join (select SUM(d.service_actual_pay) as serviceActualPay,SUM(d.deduction) as deduction," +
                "id2 from doctor_change_price d where d.status='1' and d.reservations_id ='" + reservationsId + "' group by id2) t3 " +
                "ON t1.id2 = t3.id2 left join " + areaAbbreviation+"pay_service_info t4 on t2.pay_service_id=t4.id where 1=1 ";
        List<PayServicedInfo> openBillList3 = sqlMapper.selectList(sql3, PayServicedInfo.class);
        return openBillList3;
    }

    public int ensure(String customerId, String reservationsId, String[] str, String doctor, String remarks, String abbreviation) {
        String leftUp = "";
        String leftDown = "";
        String rightUp = "";
        String rightDown = "";
        String consumableName = "";
        String arrivalStore = "";
        String number = "";
        String amount = "";
        String amountActually = "";
        String price = "";
        String type = "";
        for (int i = 0; i < str.length; i++) {
            leftUp = str[i].split("#")[0];
            rightUp = str[i].split("#")[1];
            rightDown = str[i].split("#")[2];
            leftDown = str[i].split("#")[3];
            consumableName = str[i].split("#")[4];
            arrivalStore = str[i].split("#")[5];
            number = str[i].split("#")[6];
            price = str[i].split("#")[7];
            amount = str[i].split("#")[8];
            amountActually = str[i].split("#")[9];
            type = str[i].split("#")[10];
            String sql1 = "select medical_record_number from " + abbreviation+"customer_info where id='" + customerId + "' limit 1";
            CustomerInfo info = sqlMapper.selectOne(sql1, CustomerInfo.class);
            String sql = " INSERT INTO doctor_consumable (customer_id,reservations_id,left_up,left_down, " +
                    " right_up,right_down, consumable_name,arrival_store,amount,amount_actually,number ,price,type, remarks, create_time,doctor,medical_record_number) values  " +
                    " ('" + customerId + "','" + reservationsId + "','" + leftUp + "','" + leftDown + "','" + rightUp + "', " +
                    " '" + rightDown + "','" + consumableName + "','" + arrivalStore + "','" + amount + "', '" + amountActually + "','" + number + "' ,'" + price + "','" + type + "'," +
                    " '" + remarks + "', '" + DateTimeKit.now() + "','" + doctor + "','" + info.getMedicalRecordNumber() + "') ";
            sqlMapper.insert(sql);
        }
        return 1;
    }

    public int checkOut(String customerId, String reservationsId, String[] str, String doctor, String remarks, String processUnit, String status, String store, String picker, String phone, String deliveryTime, String pickUpTime, String abbreviation) {
        String time = DateTimeKit.now().substring(0, 10).replace("-", "");
        Random rand = new Random();
        int randomNumber = rand.nextInt(999999);
        time = time + randomNumber;
        String leftUp = "";
        String leftDown = "";
        String rightUp = "";
        String rightDown = "";
        String payServiceName = "";
        String number = "";
        String amount = "";
        String amountActually = "";
        String price = "";
        String type = "";
        for (int i = 0; i < str.length; i++) {
            leftUp = str[i].split("#")[0];
            rightUp = str[i].split("#")[1];
            rightDown = str[i].split("#")[2];
            leftDown = str[i].split("#")[3];
            payServiceName = str[i].split("#")[4];
            number = str[i].split("#")[5];
            amount = str[i].split("#")[6];
            amountActually = str[i].split("#")[7];
            price = str[i].split("#")[8];
            type = str[i].split("#")[9];
            String sql1 = "select medical_record_number from " + abbreviation+"customer_info where id='" + customerId + "' limit 1";
            CustomerInfo info = sqlMapper.selectOne(sql1, CustomerInfo.class);
            String sql = " INSERT INTO doctor_technology_processing (order_number,customer_id,reservations_id,left_up,left_down, " +
                    " right_up,right_down,arrival_store, pay_service_name,amount,amount_actually,price,number ,type, remarks, create_time,doctor,process_unit,status,medical_record_number,picker,phone,delivery_time,pick_up_time) values  " +
                    " ('" + time + "','" + customerId + "','" + reservationsId + "','" + leftUp + "','" + leftDown + "','" + rightUp + "', " +
                    " '" + rightDown + "','" + store + "','" + payServiceName + "','" + amount + "','" + amountActually + "','" + price + "' ,'" + number + "' ,'" + type + "', " +
                    "'" + remarks + "', '" + DateTimeKit.now() + "','" + doctor + "','" + processUnit + "','" + status + "'," +
                    "'" + info.getMedicalRecordNumber() + "','" + picker + "','" + phone + "','" + deliveryTime + "','" + pickUpTime + "') ";
            sqlMapper.insert(sql);
        }
        return 1;
    }

    public List<DoctorTechnologyInfo> technologyTableList(String reservationsId, String abbreviation) {
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select t.id as id,t.reservations_id as reservationsId,t.arrival_store as arrivalStore,t.amount_actually as amountActually,t.price as price,t.customer_id as customerId,left(t.create_time,19) as createTime,t.doctor as doctor,t.remarks as remarks," +
                "t.left_up as leftUp,t.right_up as rightUp,t.left_down as leftDown,t.right_down as rightDown,t.consumable_name as consumableName,t.amount as amount," +
                "t.number as number,t.type as type,r.customer_name as customerName from doctor_consumable t " +
                "left join " + abbreviation+"customer_info r on t.customer_id = r.id where t.reservations_id='" + reservationsId + "'";
        List<DoctorTechnologyInfo> list = sqlMapper.selectList(sql, DoctorTechnologyInfo.class);
        return list;
    }

    public List<DoctorTechnologyProcessingInfo> processingTableList(String reservationsId, String abbreviation) {
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select t.id as id,t.reservations_id as reservationsId,t.customer_id as customerId,t.price as price,t.amount_actually as amountActually,t.arrival_store as arrivalStore,t.status as status,left(t.create_time,19) as createTime,t.doctor as doctor,t.remarks as remarks," +
                "t.left_up as leftUp,t.right_up as rightUp,t.left_down as leftDown,t.right_down as rightDown,t.pay_service_name as payServiceName,t.amount as amount," +
                "t.number as number,t.type as type,t.discount_price as discountPrice ,t.process_unit as processUnit,r.customer_name as customerName from doctor_technology_processing t " +
                "left join " + abbreviation+"customer_info r on t.customer_id = r.id where t.reservations_id='" + reservationsId + "'";
        List<DoctorTechnologyProcessingInfo> list = sqlMapper.selectList(sql, DoctorTechnologyProcessingInfo.class);
        return list;
    }

    public int updateTechnology(String doctor, String id, String remarks, String leftUp, String rightUp, String leftDown, String rightDown, String arrivalStore, String payServiceName, String number, Double price, String type, String processUnit, String status, Double amount, Double amountActually) {
        String sql = "select * from doctor_technology_processing where id='" + id + "' limit 1";
        DoctorTechnologyProcessingInfo info = sqlMapper.selectOne(sql, DoctorTechnologyProcessingInfo.class);
        if (leftUp == null || leftUp == "") {
            leftUp = info.getLeftUp();
        }
        if (rightUp == null || rightUp == "") {
            rightUp = info.getRightUp();
        }
        if (leftDown == null || leftDown == "") {
            leftDown = info.getLeftDown();
        }
        if (rightDown == null || rightDown == "") {
            rightDown = info.getRightDown();
        }
        if (remarks == null || remarks == "") {
            remarks = info.getRemarks();
        }

        String sql1 = "update doctor_technology_processing t set t.doctor='" + doctor + "',t.remarks='" + remarks + "',t.left_up='" + leftUp + "',t.right_up='" + rightUp + "',t.left_down='" + leftDown + "'," +
                "t.right_down='" + rightDown + "',t.arrival_store='" + arrivalStore + "',t.pay_service_name='" + payServiceName + "',t.amount='" + amount + "',t.price='" + price + "'," +
                "t.number='" + number + "',t.type='" + type + "',t.process_unit='" + processUnit + "',t.amount_actually='" + amountActually + "',t.status='" + status + "' where id='" + id + "'";
        return sqlMapper.update(sql1);
    }

    public int updateConsumable(String doctor, String id, String remarks, String leftUp, String rightUp, String leftDown, String rightDown, String arrivalStore, String consumableName, String number, String type, Double price, Double amount, Double amountActually) {
        String sql = "select * from doctor_consumable where id='" + id + "' limit 1";
        DoctorTechnologyInfo info = sqlMapper.selectOne(sql, DoctorTechnologyInfo.class);
        if (leftUp == null || leftUp == "") {
            leftUp = info.getLeftUp();
        }
        if (rightUp == null || rightUp == "") {
            rightUp = info.getRightUp();
        }
        if (leftDown == null || leftDown == "") {
            leftDown = info.getLeftDown();
        }
        if (rightDown == null || rightDown == "") {
            rightDown = info.getRightDown();
        }
        if (doctor == null || doctor == "") {
            doctor = info.getDoctor();
        }
        if (remarks == null || remarks == "") {
            remarks = info.getRemarks();
        }
        String sql1 = "update doctor_consumable t set t.doctor='" + doctor + "',t.remarks='" + remarks + "',t.left_up='" + leftUp + "',t.right_up='" + rightUp + "',t.left_down='" + leftDown + "'," +
                "t.right_down='" + rightDown + "',t.arrival_store='" + arrivalStore + "',t.consumable_name='" + consumableName + "',t.amount_actually='" + amountActually + "',t.amount='" + amount + "'," +
                "t.number='" + number + "',t.price ='" + price + "' ,t.type='" + type + "' where id='" + id + "'";
        return sqlMapper.update(sql1);
    }

    public int deleteProcess(String ids) {
        if (ToolUtil.isEmpty(ids)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql1 = "delete from doctor_technology_processing where id='" + ids + "'";
        return sqlMapper.delete(sql1);
    }

    public int deleteTec(String ids) {
        if (ToolUtil.isEmpty(ids)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql1 = "delete from doctor_consumable where id='" + ids + "'";
        return sqlMapper.delete(sql1);
    }

    public List<DoctorChangePriceInfo> selectDoctorPerformanceGetMap(Page<DoctorChangePriceInfo> page, SearchDoctorChangePriceInfo searchInfo,String abbreviation,String userAccount) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore())) {
            params.put("arrivalStore", searchInfo.getArrivalStore());
        }
        if (StringUtil.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartCreateTime())) {
            params.put("startCreateTime", searchInfo.getStartCreateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndCreateTime())) {
            params.put("endCreateTime", searchInfo.getEndCreateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        } else {
            String sql = "select b.id as roleid from user a left join role b on a.roleId=b.id where a.account='" + userAccount + "'";
            User user = sqlMapper.selectOne(sql, User.class);
            if (user != null) {
                if ("1".equals(user.getRoleid()) || "1,".equals(user.getRoleid().substring(0, 2))) {
                    params.put("doctor", "");
                } else {
                    params.put("doctor", userAccount);
                }
            }
        }
        String tableName1 = abbreviation + "triage_performance";
        params.put("tableName", abbreviation+"customer_info");
        params.put("tableName1", tableName1);
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryPerformance(page, params);
    }

    public List<DoctorChangePriceInfo> selectDoctorPerformanceGetFen(Page<DoctorChangePriceInfo> page, SearchDoctorChangePriceInfo searchInfo,String abbreviation,String userAccount) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore())) {
            params.put("arrivalStore", searchInfo.getArrivalStore());
        }
        if (StringUtil.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartCreateTime())) {
            params.put("startCreateTime", searchInfo.getStartCreateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndCreateTime())) {
            params.put("endCreateTime", searchInfo.getEndCreateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        } else {
            String sql = "select b.id as roleid from user a left join role b on a.roleId=b.id where a.account='" + userAccount + "'";
            User user = sqlMapper.selectOne(sql, User.class);
            if (user != null) {
                if ("1".equals(user.getRoleid()) || "1,".equals(user.getRoleid().substring(0, 2))) {
                    params.put("doctor", "");
                } else {
                    params.put("doctor", userAccount);
                }
            }
        }
        String tableName1 = abbreviation + "triage_performance";
        params.put("tableName", abbreviation+"customer_info");
        params.put("tableName1", tableName1);
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryPerformanceFen(page, params);
    }

    public int getQueryPagesPerformanceCount(Page<DoctorChangePriceInfo> page, SearchDoctorChangePriceInfo searchInfo, String abbreviation, String userAccount) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore())) {
            params.put("arrivalStore", searchInfo.getArrivalStore());
        }
        if (StringUtil.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartCreateTime())) {
            params.put("startCreateTime", searchInfo.getStartCreateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndCreateTime())) {
            params.put("endCreateTime", searchInfo.getEndCreateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        } else {
            String sql = "select b.id as roleid from user a left join role b on a.roleId=b.id where a.account='" + userAccount + "'";
            User user = sqlMapper.selectOne(sql, User.class);
            if (user != null) {
                if ("1".equals(user.getRoleid()) || "1,".equals(user.getRoleid().substring(0, 2))) {
                    params.put("doctor", "");
                } else {
                    params.put("doctor", userAccount);
                }
            }
        }
        String tableName1 = abbreviation + "triage_performance";
        params.put("tableName", abbreviation+"customer_info");
        params.put("tableName1", tableName1);
        return dao.getQueryPagesPerformanceCount(page, params);
    }

    public int getQueryPagesCountFen(Page<DoctorChangePriceInfo> page, SearchDoctorChangePriceInfo searchInfo, String abbreviation, String userAccount) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore())) {
            params.put("arrivalStore", searchInfo.getArrivalStore());
        }
        if (StringUtil.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartCreateTime())) {
            params.put("startCreateTime", searchInfo.getStartCreateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndCreateTime())) {
            params.put("endCreateTime", searchInfo.getEndCreateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        } else {
            String sql = "select b.id as roleid from user a left join role b on a.roleId=b.id where a.account='" + userAccount + "'";
            User user = sqlMapper.selectOne(sql, User.class);
            if (user != null) {
                if ("1".equals(user.getRoleid()) || "1,".equals(user.getRoleid().substring(0, 2))) {
                    params.put("doctor", "");
                } else {
                    params.put("doctor", userAccount);
                }
            }
        }
        String tableName1 = abbreviation + "triage_performance";
        params.put("tableName", abbreviation+"customer_info");
        params.put("tableName1", tableName1);
        return dao.getQueryPagesCountFen(page, params);
    }

    public List<CaseInfo> caseTableList(String customerId, String abbreviation) {
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select * from case_info c," + abbreviation+"customer_info w  where  c.customer_id = w.id and customer_id = '" + customerId + "' order by c.operation_time desc; ";
        List<CaseInfo> list = sqlMapper.selectList(sql, CaseInfo.class);
        return list;
    }

    public int DeleteRevisit(String id, String userAccount, String roleName, String userId, String outpatientName) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String selSql = "select * from return_doctor_visit_info where id='" + id + "' ";
        DoctorRevisitInfo info = sqlMapper.selectOne(selSql, DoctorRevisitInfo.class);
        if ((info.getRevisit()).equals(userAccount) || roleName.indexOf("前台") != -1) {
            String sql = "delete from return_doctor_visit_info  where id = '" + id + "'";
            //插入随访日志
            String sq11 = "INSERT INTO `doctor_visit_info_log` (createtime,single_person,logtype, `userid`," +
                    " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" + userAccount + "','删除随访','" + userId + "','成功','" + outpatientName +
                    "','删除时间：" + DateTimeKit.now() + "','" + info.getCustomerId() + "','" + info.getReservationsId() + "');";
            sqlMapper.insert(sq11);
            return sqlMapper.delete(sql);
        } else {
            return -1;
        }
    }

    public int updateCustomerArrivalStatus(int arrivalStatus, String customerId, String abbreviation) {
        String sql = "update " + abbreviation+"customer_info set arrival_status = " + arrivalStatus + " where id = '" + customerId + "'";
        return sqlMapper.update(sql);
    }

    public String insert(CaseInfo info) {
        String id = CommUtils.getUUID();
        Random rand = new Random();
        int randomNumber = rand.nextInt(999999);
        id = id + randomNumber;
        String finnallyTime = "";
        if (info.getFinnallyTime() == null || info.getFinnallyTime() == "") {
            finnallyTime = "null";
        } else {
            finnallyTime = "'" + info.getFinnallyTime() + "'";
        }
        String sql = "insert into case_info (`id`,`customer_id`,`reservations_id`,`zhushu`,`doctor_id`,`nurse_id`,`type`,`now_medical_history`,`before_medical_history`,`finnally_time`,`doctor_advice`,`lable`,remarkss" +
                ",mouth_check ,auxiliary_check ,diagnose ,remedy_plan ,dispose ,pay_service_id,status,arrival_store,abbreviation,founder,update_time) VALUES" +
                "('" + id + "','" + info.getCustomerId() + "','" + info.getReservationsId() + "','" + info.getZhushu() + "','" + info.getDoctorId() + "','" + info.getNurseId() + "','" + info.getType() + "','" + info.getNowMedicalHistory() + "','" + info.getBeforeMedicalHistory() + "'" +
                "," + finnallyTime + ",'" + info.getDoctorAdvice() + "' ,'" + info.getLable() + "','" + info.getRemarks() + "','1','2','3','4','5'" +
                ",'" + info.getPayServiceId() + "'," + info.getStatus() + ",'" + info.getOperationTime()
                + "','" + info.getAbbreviation() + "','" + info.getUserAccount()
                + "','" + DateTimeKit.now() + "');";
        sqlMapper.insert(sql);
        String case_id = id;
        //第一组检查循环插入
        String[] leftUp1 = info.getLeftUp1().split("&&", -1);
        String[] rightUp1 = info.getRightUp1().split("&&", -1);
        String[] leftDown1 = info.getLeftDown1().split("&&", -1);
        String[] rightDown1 = info.getRightDown1().split("&&", -1);
        String[] remarks1 = info.getRemarks1().split("&&", -1);
        for (int i = 0; i < leftUp1.length; i++) {
            String sql1 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','1','" + leftUp1[i] + "','" + rightUp1[i] + "','" + leftDown1[i] + "','" + rightDown1[i] + "','" + remarks1[i] + "')";
            sqlMapper.insert(sql1);
        }
        //第二组检查循环插入
        String[] leftUp2 = info.getLeftUp2().split("&&", -1);
        String[] rightUp2 = info.getRightUp2().split("&&", -1);
        String[] leftDown2 = info.getLeftDown2().split("&&", -1);
        String[] rightDown2 = info.getRightDown2().split("&&", -1);
        String[] remarks2 = info.getRemarks2().split("&&", -1);
        for (int i = 0; i < leftUp2.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','2','" + leftUp2[i] + "','" + rightUp2[i] + "','" + leftDown2[i] + "','" + rightDown2[i] + "','" + remarks2[i] + "')";
            sqlMapper.insert(sql2);
        }
        //第三组检查循环插入
        String[] leftUp3 = info.getLeftUp3().split("&&", -1);
        String[] rightUp3 = info.getRightUp3().split("&&", -1);
        String[] leftDown3 = info.getLeftDown3().split("&&", -1);
        String[] rightDown3 = info.getRightDown3().split("&&", -1);
        String[] remarks3 = info.getRemarks3().split("&&", -1);
        for (int i = 0; i < leftUp3.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','3','" + leftUp3[i] + "','" + rightUp3[i] + "','" + leftDown3[i] + "','" + rightDown3[i] + "','" + remarks3[i] + "')";
            sqlMapper.insert(sql2);
        }
        //第四组检查循环插入
        String[] leftUp4 = info.getLeftUp4().split("&&", -1);
        String[] rightUp4 = info.getRightUp4().split("&&", -1);
        String[] leftDown4 = info.getLeftDown4().split("&&", -1);
        String[] rightDown4 = info.getRightDown4().split("&&", -1);
        String[] remarks4 = info.getRemarks4().split("&&", -1);
        for (int i = 0; i < leftUp4.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','4','" + leftUp4[i] + "','" + rightUp4[i] + "','" + leftDown4[i] + "','" + rightDown4[i] + "','" + remarks4[i] + "')";
            sqlMapper.insert(sql2);
        }
        //第五组检查循环插入
        String[] leftUp5 = info.getLeftUp5().split("&&", -1);
        String[] rightUp5 = info.getRightUp5().split("&&", -1);
        String[] leftDown5 = info.getLeftDown5().split("&&", -1);
        String[] rightDown5 = info.getRightDown5().split("&&", -1);
        String[] remarks5 = info.getRemarks5().split("&&", -1);
        for (int i = 0; i < leftUp5.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','5','" + leftUp5[i] + "','" + rightUp5[i] + "','" + leftDown5[i] + "','" + rightDown5[i] + "','" + remarks5[i] + "')";
            sqlMapper.insert(sql2);
        }
        String sql1 = "update " + info.getAbbreviation()+"customer_info set is_status =1 where id='" + info.getCustomerId() + "'";
        sqlMapper.update(sql1);
        String getcust = "select * from " + info.getAbbreviation()+"customer_info where id='" + info.getCustomerId() + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(getcust, CustomerInfo.class);
        //插入新增日志
        String inertsq1 = "INSERT INTO `case_info_log` (createtime,reservations_id,case_id,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + customerInfo.getReservationsId() + "','" + case_id + "','" + info.getUserAccount() + "','新增病历','" + info.getUserIdValue() + "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + info.getOperationTime() +
                "','" + info.getUserAccount() + " 把 " + customerInfo.getCustomerName() + " 的病历在:" + DateTimeKit.now() + "进行添加','" + customerInfo.getId() + "');";
        sqlMapper.insert(inertsq1);
        return "1"+","+id;
    }

    public int updateStatusS(String id) {
        String sql = "update case_info a set a.status='2' where id='" + id + "'";
        return sqlMapper.update(sql);
    }

    public String insert1(CaseInfo info) {
        String id = CommUtils.getUUID();
        Random rand = new Random();
        int randomNumber = rand.nextInt(999999);
        id = id + randomNumber;
        String finnallyTime = "";
        if (info.getFinnallyTime() == null || info.getFinnallyTime() == "") {
            finnallyTime = "null";
        } else {
            finnallyTime = "'" + info.getFinnallyTime() + "'";
        }
        String sql = "insert into case_info (`id`,`customer_id`,`reservations_id`,`zhushu`,`doctor_id`,`nurse_id`,`type`,`now_medical_history`,`before_medical_history`,`finnally_time`,`doctor_advice`,`lable`,remarkss" +
                ",mouth_check ,auxiliary_check ,diagnose ,remedy_plan ,dispose ,pay_service_id,status,arrival_store,abbreviation,founder,update_time) VALUES" +
                "('" + id + "','" + info.getCustomerId() + "','" + info.getReservationsId() + "','" + info.getZhushu() + "','" + info.getDoctorId() + "','" + info.getNurseId() + "','" + info.getType() + "','" + info.getNowMedicalHistory() + "','" + info.getBeforeMedicalHistory() + "'" +
                "," + finnallyTime + ",'" + info.getDoctorAdvice() + "' ,'" + info.getLable() + "','" + info.getRemarks() + "','1','2','3','4','5'" +
                ",'" + info.getPayServiceId() + "'," + info.getStatus() + ",'" + info.getOperationTime()
                + "','" + info.getAbbreviation() + "','" + info.getUserAccount()
                + "','" + DateTimeKit.now() + "');";
        sqlMapper.insert(sql);
        String case_id = id;
        //第一组检查循环插入
        String[] leftUp1 = info.getLeftUp1().split("&&", -1);//
        String[] rightUp1 = info.getRightUp1().split("&&", -1);
        String[] leftDown1 = info.getLeftDown1().split("&&", -1);
        String[] rightDown1 = info.getRightDown1().split("&&", -1);
        String[] remarks1 = info.getRemarks1().split("&&", -1);
        for (int i = 0; i < leftUp1.length; i++) {
            String sql1 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','1','" + leftUp1[i] + "','" + rightUp1[i] + "','" + leftDown1[i] + "','" + rightDown1[i] + "','" + remarks1[i] + "')";
            sqlMapper.insert(sql1);
        }
        //第二组检查循环插入
        String[] leftUp2 = info.getLeftUp2().split("&&", -1);
        String[] rightUp2 = info.getRightUp2().split("&&", -1);
        String[] leftDown2 = info.getLeftDown2().split("&&", -1);
        String[] rightDown2 = info.getRightDown2().split("&&", -1);
        String[] remarks2 = info.getRemarks2().split("&&", -1);
        for (int i = 0; i < leftUp2.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','2','" + leftUp2[i] + "','" + rightUp2[i] + "','" + leftDown2[i] + "','" + rightDown2[i] + "','" + remarks2[i] + "')";
            sqlMapper.insert(sql2);
        }
        //第三组检查循环插入
        String[] leftUp3 = info.getLeftUp3().split("&&", -1);
        String[] rightUp3 = info.getRightUp3().split("&&", -1);
        String[] leftDown3 = info.getLeftDown3().split("&&", -1);
        String[] rightDown3 = info.getRightDown3().split("&&", -1);
        String[] remarks3 = info.getRemarks3().split("&&", -1);
        for (int i = 0; i < leftUp3.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','3','" + leftUp3[i] + "','" + rightUp3[i] + "','" + leftDown3[i] + "','" + rightDown3[i] + "','" + remarks3[i] + "')";
            sqlMapper.insert(sql2);
        }
        //第四组检查循环插入
        String[] leftUp4 = info.getLeftUp4().split("&&", -1);
        String[] rightUp4 = info.getRightUp4().split("&&", -1);
        String[] leftDown4 = info.getLeftDown4().split("&&", -1);
        String[] rightDown4 = info.getRightDown4().split("&&", -1);
        String[] remarks4 = info.getRemarks4().split("&&", -1);
        for (int i = 0; i < leftUp4.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','4','" + leftUp4[i] + "','" + rightUp4[i] + "','" + leftDown4[i] + "','" + rightDown4[i] + "','" + remarks4[i] + "')";
            sqlMapper.insert(sql2);
        }
        //第五组检查循环插入
        String[] leftUp5 = info.getLeftUp5().split("&&", -1);
        String[] rightUp5 = info.getRightUp5().split("&&", -1);
        String[] leftDown5 = info.getLeftDown5().split("&&", -1);
        String[] rightDown5 = info.getRightDown5().split("&&", -1);
        String[] remarks5 = info.getRemarks5().split("&&", -1);
        for (int i = 0; i < leftUp5.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','5','" + leftUp5[i] + "','" + rightUp5[i] + "','" + leftDown5[i] + "','" + rightDown5[i] + "','" + remarks5[i] + "')";
            sqlMapper.insert(sql2);
        }
        String sql1 = "update " + info.getAbbreviation()+"customer_info set is_status =1,arrival_status =4 where id='" + info.getCustomerId() + "'";
        sqlMapper.update(sql1);
        String getcust = "select * from " + info.getAbbreviation()+"customer_info where id='" + info.getCustomerId() + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(getcust, CustomerInfo.class);
        //插入新增日志
        String inertsq1 = "INSERT INTO `case_info_log` (createtime,reservations_id,case_id,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + customerInfo.getReservationsId() + "','" + case_id + "','" + info.getUserAccount() + "','新增病历','" + info.getUserIdValue() + "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + info.getOperationTime() +
                "','" + info.getUserAccount() + " 把 " + customerInfo.getCustomerName() + " 的病历在:" + DateTimeKit.now() + "进行添加','" + customerInfo.getId() + "');";
        sqlMapper.insert(inertsq1);
        return "1,"+id;
    }

    public int update(CaseInfo info,String userAccount, String abbreviation, String userId, String outpatientName) {
        String selectSql2 = "select * from " + abbreviation+"customer_info t where id='" + info.getCustomerId() + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(selectSql2, CustomerInfo.class);
        //根据传入的病例ID，删除掉病例原有关联的牙位信息
        String sql = "delete from tooth_site where case_id ='" + info.getId() + "'";
        sqlMapper.delete(sql);
        //重新插入牙位表信息
        String case_id = info.getId();
        //第一组检查循环插入
        String[] leftUp1 = info.getLeftUp1().split("&&", -1);
        String[] rightUp1 = info.getRightUp1().split("&&", -1);
        String[] leftDown1 = info.getLeftDown1().split("&&", -1);
        String[] rightDown1 = info.getRightDown1().split("&&", -1);
        String[] remarks1 = info.getRemarks1().split("&&", -1);
        for (int i = 0; i < leftUp1.length; i++) {
            String sql1 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','1','" + leftUp1[i] + "','" + rightUp1[i] + "','" + leftDown1[i] + "','" + rightDown1[i] + "','" + remarks1[i] + "')";
            sqlMapper.insert(sql1);
        }
        //第二组检查循环插入
        String[] leftUp2 = info.getLeftUp2().split("&&", -1);
        String[] rightUp2 = info.getRightUp2().split("&&", -1);
        String[] leftDown2 = info.getLeftDown2().split("&&", -1);
        String[] rightDown2 = info.getRightDown2().split("&&", -1);
        String[] remarks2 = info.getRemarks2().split("&&", -1);
        for (int i = 0; i < leftUp2.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','2','" + leftUp2[i] + "','" + rightUp2[i] + "','" + leftDown2[i] + "','" + rightDown2[i] + "','" + remarks2[i] + "')";
            sqlMapper.insert(sql2);
        }
        //第三组检查循环插入
        String[] leftUp3 = info.getLeftUp3().split("&&", -1);
        String[] rightUp3 = info.getRightUp3().split("&&", -1);
        String[] leftDown3 = info.getLeftDown3().split("&&", -1);
        String[] rightDown3 = info.getRightDown3().split("&&", -1);
        String[] remarks3 = info.getRemarks3().split("&&", -1);
        for (int i = 0; i < leftUp3.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','3','" + leftUp3[i] + "','" + rightUp3[i] + "','" + leftDown3[i] + "','" + rightDown3[i] + "','" + remarks3[i] + "')";
            sqlMapper.insert(sql2);
        }
        //第四组检查循环插入
        String[] leftUp4 = info.getLeftUp4().split("&&", -1);
        String[] rightUp4 = info.getRightUp4().split("&&", -1);
        String[] leftDown4 = info.getLeftDown4().split("&&", -1);
        String[] rightDown4 = info.getRightDown4().split("&&", -1);
        String[] remarks4 = info.getRemarks4().split("&&", -1);
        for (int i = 0; i < leftUp4.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','4','" + leftUp4[i] + "','" + rightUp4[i] + "','" + leftDown4[i] + "','" + rightDown4[i] + "','" + remarks4[i] + "')";
            sqlMapper.insert(sql2);
        }
        //第五组检查循环插入
        String[] leftUp5 = info.getLeftUp5().split("&&", -1);
        String[] rightUp5 = info.getRightUp5().split("&&", -1);
        String[] leftDown5 = info.getLeftDown5().split("&&", -1);
        String[] rightDown5 = info.getRightDown5().split("&&", -1);
        String[] remarks5 = info.getRemarks5().split("&&", -1);
        for (int i = 0; i < leftUp5.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','5','" + leftUp5[i] + "','" + rightUp5[i] + "','" + leftDown5[i] + "','" + rightDown5[i] + "','" + remarks5[i] + "')";
            sqlMapper.insert(sql2);
        }
        //根据病例ID修改对应的病例信息
        String sql6 = "update case_info a set a.zhushu='" + info.getZhushu().trim() + "',a.now_medical_history='" + info.getNowMedicalHistory().trim() + "'," +
                "a.doctor_id='" + info.getDoctorId().trim() + "',a.nurse_id='" + info.getNurseId().trim() + "'," +
                "a.before_medical_history='" + info.getBeforeMedicalHistory().trim() + "'," +
                "a.doctor_advice='" + info.getDoctorAdvice().trim() + "',a.type='" + info.getType() + "'," +
                "a.remarkss='" + info.getRemarks() + "',a.finnally_time='" + info.getFinnallyTime() + "' ," +
                "a.pay_service_id='" + info.getPayServiceId() + "',a.status=" + info.getStatus() +
                " where id = '" + info.getId() + "'";
        //编辑病历 插入日志
        String inertsq1 = "INSERT INTO `case_info_log` (createtime,reservations_id,case_id,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + customerInfo.getReservationsId() + "','" + case_id + "','" + userAccount + "','编辑病历','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                "','" + userAccount + " 把 " + customerInfo.getCustomerName() + " 的病历在:" + DateTimeKit.now() + "进行修改','" + info.getCustomerId() + "');";
        sqlMapper.insert(inertsq1);
        return sqlMapper.update(sql6);
    }

    public int updateFinsh(CaseInfo info, String abbreviation) {
        //根据传入的病例ID，删除掉病例原有关联的牙位信息
        String sql = "delete from tooth_site where case_id ='" + info.getId() + "'";
        sqlMapper.delete(sql);
        //重新插入牙位表信息
        String case_id = info.getId();
        //第一组检查循环插入
        String[] leftUp1 = info.getLeftUp1().split("&&", -1);
        String[] rightUp1 = info.getRightUp1().split("&&", -1);
        String[] leftDown1 = info.getLeftDown1().split("&&", -1);
        String[] rightDown1 = info.getRightDown1().split("&&", -1);
        String[] remarks1 = info.getRemarks1().split("&&", -1);
        for (int i = 0; i < leftUp1.length; i++) {
            String sql1 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','1','" + leftUp1[i] + "','" + rightUp1[i] + "','" + leftDown1[i] + "','" + rightDown1[i] + "','" + remarks1[i] + "')";
            sqlMapper.insert(sql1);
        }
        //第二组检查循环插入
        String[] leftUp2 = info.getLeftUp2().split("&&", -1);
        String[] rightUp2 = info.getRightUp2().split("&&", -1);
        String[] leftDown2 = info.getLeftDown2().split("&&", -1);
        String[] rightDown2 = info.getRightDown2().split("&&", -1);
        String[] remarks2 = info.getRemarks2().split("&&", -1);
        for (int i = 0; i < leftUp2.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','2','" + leftUp2[i] + "','" + rightUp2[i] + "','" + leftDown2[i] + "','" + rightDown2[i] + "','" + remarks2[i] + "')";
            sqlMapper.insert(sql2);
        }
        //第三组检查循环插入
        String[] leftUp3 = info.getLeftUp3().split("&&", -1);
        String[] rightUp3 = info.getRightUp3().split("&&", -1);
        String[] leftDown3 = info.getLeftDown3().split("&&", -1);
        String[] rightDown3 = info.getRightDown3().split("&&", -1);
        String[] remarks3 = info.getRemarks3().split("&&", -1);
        for (int i = 0; i < leftUp3.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','3','" + leftUp3[i] + "','" + rightUp3[i] + "','" + leftDown3[i] + "','" + rightDown3[i] + "','" + remarks3[i] + "')";
            sqlMapper.insert(sql2);
        }
        //第四组检查循环插入
        String[] leftUp4 = info.getLeftUp4().split("&&", -1);
        String[] rightUp4 = info.getRightUp4().split("&&", -1);
        String[] leftDown4 = info.getLeftDown4().split("&&", -1);
        String[] rightDown4 = info.getRightDown4().split("&&", -1);
        String[] remarks4 = info.getRemarks4().split("&&", -1);
        for (int i = 0; i < leftUp4.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','4','" + leftUp4[i] + "','" + rightUp4[i] + "','" + leftDown4[i] + "','" + rightDown4[i] + "','" + remarks4[i] + "')";
            sqlMapper.insert(sql2);
        }
        //第五组检查循环插入
        String[] leftUp5 = info.getLeftUp5().split("&&", -1);
        String[] rightUp5 = info.getRightUp5().split("&&", -1);
        String[] leftDown5 = info.getLeftDown5().split("&&", -1);
        String[] rightDown5 = info.getRightDown5().split("&&", -1);
        String[] remarks5 = info.getRemarks5().split("&&", -1);
        for (int i = 0; i < leftUp5.length; i++) {
            String sql2 = "insert into tooth_site (case_id,ya_type,left_up,right_up,left_down,right_down,remarks) VALUES" +
                    "('" + case_id + "','5','" + leftUp5[i] + "','" + rightUp5[i] + "','" + leftDown5[i] + "','" + rightDown5[i] + "','" + remarks5[i] + "')";
            sqlMapper.insert(sql2);
        }
        //根据病例ID修改对应的病例信息
        String sql6 = "update case_info a set a.zhushu='" + info.getZhushu().trim() + "',a.now_medical_history='" + info.getNowMedicalHistory().trim() + "'," +
                "a.doctor_id='" + info.getDoctorId().trim() + "',a.nurse_id='" + info.getNurseId().trim() + "'," +
                "a.before_medical_history='" + info.getBeforeMedicalHistory().trim() + "'," +
                "a.doctor_advice='" + info.getDoctorAdvice().trim() + "',a.type='" + info.getType() + "'," +
                "a.remarkss='" + info.getRemarks() + "',a.finnally_time='" + info.getFinnallyTime() + "' ," +
                "a.pay_service_id='" + info.getPayServiceId() + "',a.status=" + info.getStatus() +
                " where id = '" + info.getId() + "'";
        String sql2 = "update " + abbreviation+"customer_info set arrival_status =4 where id='" + info.getCustomerId() + "'";
        sqlMapper.update(sql2);
        return sqlMapper.update(sql6);
    }

    public int choiceTc(String caseId, String statusic) {
        if (ToolUtil.isEmpty(caseId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sq1 = "update case_info a set a.statusic='" + statusic + "' where id='" + caseId + "'";
        return sqlMapper.update(sq1);
    }

    public int addBoLogValue(String oneClass, String twoClass) {
        String sql = "insert into ob_log(one_class,two_class) values('" + oneClass + "','" + twoClass + "')";
        return sqlMapper.insert(sql);
    }

    public List<ConsultantInfo> change(String param1, String customerAgeEnd, String customerSex, String customerAgeStart, String doctor, String abbreviation) {
        String contitionInfo = "";
        if (StringUtil.isNotEmpty(param1)) {
            contitionInfo += " and DATE_FORMAT(t.arrival_time, '%Y-%m-%d') = '" + param1 + "' ";
        }
        if (StringUtil.isNotEmpty(customerSex)) {
            contitionInfo += " and t.customer_sex= '" + customerSex + "' ";
        }
        if (StringUtil.isNotEmpty(doctor)) {
            contitionInfo += " and t.doctor= '" + doctor + "' ";
        }
        if (StringUtil.isNotEmpty(customerAgeStart)) {
            contitionInfo += " and t.customer_age >= '" + customerAgeStart + "'  ";
        }
        if (StringUtil.isNotEmpty(customerAgeEnd)) {
            contitionInfo += " and t.customer_age <= '" + customerAgeEnd + "' ";
        }
        String sql = "select count(1) as countNum from (select t.id from  " + abbreviation+"customer_info t " +
                " where t.add_status = 1 " + contitionInfo + " group by t.id) a" +
                " union all select count(1) as countNum from (select t.id from " + abbreviation+"customer_info  t " +
                " where t.is_status = 1  " + contitionInfo + " group by t.id) a" +
                " union all select count(1) as countNum from (select t.id from " + abbreviation+"customer_info  t " +
                " where t.visit_status ='1' " + contitionInfo + " group by t.reservations_id) a;";
        List<ConsultantInfo> tongJiInfoList1 = sqlMapper.selectList(sql, ConsultantInfo.class);
        return tongJiInfoList1;
    }

    public int addsf(String remarks, String title, String userAccount) {
        String sql = "insert into follow_template (title,remarks,user) value ('" + title + "','" + remarks + "','" + userAccount + "')";
        return sqlMapper.insert(sql);
    }

    public int addjg(String remarks, String title, String userAccount) {
        String sql = "insert into follow_results_template (title,remarks,user) value ('" + title + "','" + remarks + "','" + userAccount + "')";
        return sqlMapper.insert(sql);
    }

    public int addjz(String remarks, String title, String userAccount) {
        String sql = "insert into orthodontic_template (title,remarks,user) value ('" + title + "','" + remarks + "','" + userAccount + "')";
        return sqlMapper.insert(sql);
    }

    public int addsj(String remarks, String title, String userAccount) {
        String sql = "insert into design_scheme (title,remarks,user) value ('" + title + "','" + remarks + "','" + userAccount + "')";
        return sqlMapper.insert(sql);
    }

    public int zaizhen(String zaiRemarks, String zaiPeople, String customerId, String abbreviation) {
        String sql = "update " + abbreviation+"customer_info a set a.zai_people='" + zaiPeople + "'," +
                "a.zai_remarks='" + zaiRemarks + "',a.zai_time='" + DateTimeKit.now() + "' where id='" + customerId + "'";
        return sqlMapper.update(sql);
    }

    public List<SfInfo> getSjList(String userAccount) {
        String sql = "select * from design_scheme where user='" + userAccount + "'";
        List<SfInfo> list = sqlMapper.selectList(sql, SfInfo.class);
        return list;
    }

    public List<SfInfo> getJzList(String userAccount) {
        String sql = "select * from orthodontic_template where user='" + userAccount + "'";
        List<SfInfo> list = sqlMapper.selectList(sql, SfInfo.class);
        return list;
    }

    public List<SfInfo> getSfList(String userAccount) {
        String sql = "select * from follow_template where user='" + userAccount + "'";
        List<SfInfo> list = sqlMapper.selectList(sql, SfInfo.class);
        return list;
    }

    public List<JgInfo> getJgList(String userAccount) {
        String sql = "select * from follow_results_template where user='" + userAccount + "'";
        List<JgInfo> list = sqlMapper.selectList(sql, JgInfo.class);
        return list;
    }

    public SfInfo getSfById(String id) {
        String sql = "select * from follow_template where id='" + id + "'";
        SfInfo info = sqlMapper.selectOne(sql, SfInfo.class);
        return info;
    }

    public SfInfo getJzById(String id) {
        String sql = "select * from orthodontic_template where id='" + id + "'";
        SfInfo info = sqlMapper.selectOne(sql, SfInfo.class);
        return info;
    }

    public SfInfo getSjById(String id) {
        String sql = "select * from design_scheme where id='" + id + "'";
        SfInfo info = sqlMapper.selectOne(sql, SfInfo.class);
        return info;
    }

    public SfInfo getJgById(String id) {
        String sql = "select * from follow_results_template where id='" + id + "'";
        SfInfo info = sqlMapper.selectOne(sql, SfInfo.class);
        return info;
    }

    public int deleteCaseInfo(String id, String customerId, String abbreviation, String userAccount, String userId, String outpatientName) {
        String sql1 = "delete from tooth_site where case_id = '" + id + "'";
        sqlMapper.delete(sql1);//删除病例对应的牙位信息
        String sql = "delete from case_info where id = '" + id + "'";
        String selectSql2 = "select * from " + abbreviation+"customer_info t where id='" + customerId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(selectSql2, CustomerInfo.class);
        String inertsq1 = "INSERT INTO `case_info_log` (createtime,reservations_id,case_id,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + customerInfo.getReservationsId() + "','" + id + "','" + userAccount + "','删除病历','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                "','" + userAccount + " 把 " + customerInfo.getCustomerName() + " 的病历在:" + DateTimeKit.now() + "进行删除','" + customerId + "');";
        sqlMapper.insert(inertsq1);//插入删除日志
        return sqlMapper.delete(sql);//删除病例表对应ID的病例信息
    }

    public int deleteCases(String caseId) {
        String sql1 = "delete from tooth_site where case_id = '" + caseId + "'";
        sqlMapper.delete(sql1);//删除病例对应的牙位信息
        String sql = "delete from case_info where id = '" + caseId + "'";
        return sqlMapper.delete(sql);//删除病例表对应ID的病例信息
    }

    public List<PayServicedInfo> changePriceTable(String reservationsId, String abbreviation, String userAccount,String areaAbbreviation) {
        String sql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ," +
                "t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ," +
                "t1.pay_service_num as payServiceNum ,t1.service_actual_pay as serviceActualPay ," +
                "left(t1.operation_time,19) as operationTime,t1.remarks as remarks,t1.check_status as checkStatus," +
                " t1.folding_pay as foldingPay,t1.doctor as doctor ,t1.left_up as leftUp ," +
                "t1.left_down as leftDown ,t1.right_up as rightUp ,t1.right_down as rightDown ,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                "t2.pay_service_name as payServiceName ,t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                "t3.customer_name as customerName ," +
                "t3.medical_record_number as medicalRecordNumber,t3.customer_status as customerStatus ,t3.customer_sex as customerSex ,t3.customer_phone as customerPhone,t3.customer_age as customerAge, " +
                " ( " +
                " CASE t3.customer_sex " +
                " WHEN 1 THEN " +
                "     '男' " +
                " WHEN 2 THEN " +
                "     '女' " +
                " ELSE " +
                "     '未知' " +
                " END " +
                "    ) as sex  ,t3.arrival_store as arrivalStore," +
                "t3.field_tc as fieldTc from " + abbreviation+"deal_info t1 left join " + areaAbbreviation+"pay_service_info t2 on t1.pay_service_id=t2.id left join " + abbreviation+"customer_info t3 on t1.customer_id=t3.id" +
                " where t1.reservations_Id = '" + reservationsId + "' and t1.deal_status='已结账' and t1.doctor='" + userAccount + "' group by t1.operation_time,t1.reservations_id,t1.pay_service_id";
        List<PayServicedInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServicedInfo.class);
        return payServiceInfoList;
    }

    public int updateDoctor(String doctor, String customerId, String remarks, String time, String optType, String abbreviation, String userAccount, String userId, String outpatientName) {
        String sql1 = "select id from user where account ='" + doctor + "' limit 0,1";
        User user = sqlMapper.selectOne(sql1, User.class);
        String sql2 = "select status from doctor_scheduling where doctor_id='" + user.getId() + "' and the_date='" + time + "' and outpatient_name='" + outpatientName + "'";
        DoctorSchedulingInfo info = sqlMapper.selectOne(sql2, DoctorSchedulingInfo.class);
        if (info != null) {
            if (info.getStatus().intValue() == 1 || optType.equals("1")) {//如果当前医生有排班或者需要强制执行转正,执行转诊SQL
                String sql = "update " + abbreviation+"customer_info a  set a.doctor='" + doctor + "',a.remarks='" + remarks + "',a.transfer_status='1' where id = '" + customerId + "'";
                return sqlMapper.update(sql);//update 执行后返回的是操作影响的行数,所有值肯定是大于或者等于0
            } else { //如果当前医生没有排班,就返回-2
                return -2;
            }
        } else { //如果为空,说明排班没有冲突
            String sql = "update " + abbreviation+"customer_info a  set a.doctor='" + doctor + "',a.remarks='" + remarks + "',a.transfer_status='1' where id = '" + customerId + "'";
            return sqlMapper.update(sql);
        }
    }

    public int referralerPerson(String referralerPersoned, String customerId, String remarks, String referralerPerson, String abbreviation) {
        String updateSql = "update " + abbreviation+"customer_info t set t.referraler_person='" + referralerPerson + "'," +
                " t.referraler_personed='" + referralerPersoned + "',t.referraler_time='" + DateTimeKit.now() + "'," +
                " t.is_referraler=1,t.referraler_remarks ='" + remarks + "' where id='" + customerId + "' ";
        return sqlMapper.update(updateSql);
    }

    public int deletePrice(String createTime) {
        String sql = "delete from  doctor_change_price  where create_time = '" + createTime + "'";
        return sqlMapper.delete(sql);
    }

    public int changePrice(String id2, String customerId, String payServiceName, String remarks, Double accountArrears, Double serviceActualPay, Double deductionAmount, Integer payServiceNum, String abbreviation, String userAccount) {
        String sql = "select reservations_id from " + abbreviation+"deal_info where id2='" + id2 + "' limit 0,1 ";
        PayServicedInfo info = sqlMapper.selectOne(sql, PayServicedInfo.class);
        String insertSql2 = "insert into doctor_change_price (id2,doctor,customer_id,reservations_id,pay_service_name,service_actual_pay,arrears,number,deduction,remarks,create_time,create_people)" +
                " VALUES('" + id2 + "','" + userAccount + "','" + customerId + "','" + info.getReservationsId() + "','" + payServiceName + "','" + serviceActualPay + "','" + accountArrears + "','" + payServiceNum + "','" + deductionAmount + "'," +
                "'" + remarks + "','" + DateTimeKit.now() + "','" + userAccount + "')";
        sqlMapper.insert(insertSql2);
        String sqlUpdate = "update " + abbreviation+"customer_info t set t.deduct_status='1' where id='" + customerId + "' ";
        sqlMapper.update(sqlUpdate);
        String selectSql = "select sum(service_actual_pay)+ sum(deduction) as totalPay,reservations_id as reservationsId from doctor_change_price where id2='" + id2 + "' group by reservations_id";
        DoctorChangePriceInfo selectInfo = sqlMapper.selectOne(selectSql, DoctorChangePriceInfo.class);
        BigDecimal data1 = new BigDecimal(selectInfo.getTotalPay());
        String selectSql1 = "select folding_pay from " + abbreviation+"deal_info where reservations_id='" + info.getReservationsId() + "' and id2='" + id2 + "' group by reservations_id";
        PayServicedInfo selectInfo1 = sqlMapper.selectOne(selectSql1, PayServicedInfo.class);
        BigDecimal data2 = new BigDecimal(selectInfo1.getFoldingPay());
        if (data1.compareTo(data2) == 0) {
            String updateStatus = "update " + abbreviation+"deal_info a set a.execute_status='1' where id2='" + id2 + "' ";
            return sqlMapper.update(updateStatus);
        } else {
            String updateStatus = "update " + abbreviation+"deal_info a set a.execute_status='0' where id2='" + id2 + "' ";
            return sqlMapper.update(updateStatus);
        }
    }

    public List<DoctorChangePriceInfo> getPriceInfo(String id2) {
        String sql1 = "select t.id,t.id2 as id2,doctor as doctor,t.pay_service_name as payServiceName ,t.service_actual_pay as serviceActualPay ," +
                "t.deduction as deduction ,left(t.create_time,19) as createTime from doctor_change_price t where t.status='1' and t.id2='" + id2 + "'";
        List<DoctorChangePriceInfo> infoList = sqlMapper.selectList(sql1, DoctorChangePriceInfo.class);
        return infoList;
    }

    //点亮获取病历号
    public String getMedicalRecordNumbers(String customerPhone, String abbreviation) {
        String upsql = "update " + abbreviation +"medicalrecord_info set customer_phone=null where customer_phone='" + customerPhone + "' ";
        String usql = "update " + abbreviation +"medicalrecord_info set customer_phone='" + customerPhone + "'" +
                " where customer_phone is null order by id asc limit 1 ";
        sqlMapper.update(upsql);
        sqlMapper.update(usql);
        String ssql2 = "select concat(prefix_part_one,prefix_part_two,num) as medicalRecordNumber from " + abbreviation +"medicalrecord_info where customer_phone='" + customerPhone + "' limit 1";
        CustomerInfo cinfo2 = sqlMapper.selectOne(ssql2, CustomerInfo.class);
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("tableName", abbreviation+"customer_info");
        params.put("medicalRecordNumber", cinfo2.getMedicalRecordNumber());
        return cinfo2.getMedicalRecordNumber();
    }
    public int insert(CustomerInfo info,String outpatientName,String userAccount) {
        info.setStatus("到店");
        info.setArrivalStore(outpatientName);
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
            if (info.getCustomerAge() >= 100) {
                info.setAgeRange("99+");
            }
        }
        info.setDepartment(info.getOneChannel());
        if ("null".equals(info.getHuifang()) || info.getHuifang() == null) {
            info.setHuifang("");
        }
        if ("null".equals(info.getWechatNumber()) || info.getWechatNumber() == null) {
            info.setWechatNumber("");
        }
        if ("null".equals(info.getAppointmentPayService()) || info.getAppointmentPayService() == null) {
            info.setAppointmentPayService("");
        }
        if ("null".equals(info.getFieldTc()) || info.getFieldTc() == null) {
            info.setFieldTc("");
        }
        if ("null".equals(info.getOtherContact()) || info.getOtherContact() == null) {
            info.setOtherContact("");
        }
        String sql = "INSERT INTO reservations_info (`id`,`huifang`,`department`,`appointment_time`,`tc_name`,`first_visit_date`,`age_range`,`appointment_pay_service`,`phone_shar_ship`,`outpatient_name`, `customer_name`, `customer_phone`, `customer_sex`, `customer_age`, `qq_number`," +
                " `wechat_number`,`other_contact`, `country`, `province`, `cities`, `region`, `quarters`," +
                " `marital_status`, `occupation`, `archiving`, `filing_time`, `entry_clerk`,`update_people`,`update_time`, " +
                " `one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`,`remarks`, `status`,`appointment_arrival_time`," +
                "`is_supplement`,identification_number,arrival_store,szm,search_field,customer_sex_head) VALUES" +
                "('" + info.getReservationsId() + "','" + info.getHuifang() + "','" + info.getDepartment() + "','" + DateTimeKit.now() + "','" + info.getFieldTc() + "','" + DateTimeKit.now() + "','" + info.getAgeRange() + "','" + info.getAppointmentPayService() + "','" + info.getPhoneSharShip() + "','" + info.getArrivalStore() + "','" + info.getCustomerName() + "','" + info.getCustomerPhone() + "'," + info.getCustomerSex() + "," + info.getCustomerAge() + ",'" + info.getQqNumber() + "'," +
                "'" + info.getWechatNumber() + "','" + info.getOtherContact() + "','" + info.getCountry() + "','" + info.getProvince() + "','" + info.getCities() + "','" + info.getRegion() + "'," +
                "'" + info.getAddress() + "'," + info.getMaritalStatus() + ",'" + info.getOccupation() + "','" + info.getArchiving() + "','" + DateTimeKit.now() + "','" + userAccount + "','" + userAccount + "','" + DateTimeKit.now() + "'," +
                "'" + info.getOneMedium() + "','" + info.getTwoMedium() + "','" + info.getThrMedium() + "','" + info.getOneChannel() + "','" + info.getTwoChannel() + "','" + info.getThrChannel() + "','"
                + info.getRemarks() + "', '" + info.getStatus() + "','" + DateTimeKit.now() + "','否','" + info.getMedicalRecordNumber() + "','"
                + outpatientName + "',firstconcats('" + info.getCustomerName() + "')," +
                "concat('" + info.getCustomerName() + "','" + info.getCustomerPhone() + "',firstconcats('" + info.getCustomerName() + "')," +
                "'" + info.getCustomerPhone2() + "','" + info.getWechatNumber() + "','" + DateTimeKit.now() + "','" + info.getMedicalRecordNumber() + "'),'"
                +service.getSexPt(info)+"')";
        return sqlMapper.insert(sql);
    }

    //患者第二次到店，判断是今天到店的还是以前来过，今天到店多次客户状态都是新客首次，
    public String getNewCustomerStatus(String reservationsId,String abbreviation) {
        String sql = "select id from distinction_customer_status where " +
                " reservations_id='" + reservationsId + "' and left(add_time,10)<left(now(),10) and status=1 ; ";
        List<CustomerInfo> list = sqlMapper.selectList(sql, CustomerInfo.class);
        if (list.size() > 0) {
            return getOldOrReconsultingStatus(reservationsId,abbreviation);
        } else {
            return "新客首次";
        }
    }
    //点重咨，判断之前来店是否有成交，有为老客，从来没有为新客多次
    public String getOldOrReconsultingStatus(String reservationsId,String abbreviation) {
        String sql = "select id from distinction_customer_status where " +
                " reservations_id='" + reservationsId + "' and deal_status='已成交' and left(update_time,10)<left(now(),10) and status=1 ; ";
        List<CustomerInfo> list = sqlMapper.selectList(sql, CustomerInfo.class);
        if (list.size() > 0) {
            return "老客";
        } else {
            String sql1 = "select id from "+abbreviation +"customer_info where " +
                    " reservations_id='" + reservationsId + "' and customer_status='新客多次' and left(arrival_time,10)<left(now(),10) ";
            List<CustomerInfo> list1 = sqlMapper.selectList(sql1, CustomerInfo.class);
            if(list1.size()>0){
                return "新客N次";
            }else{
                return "新客多次";
            }
        }
    }

    public CustomerInfo firstVisitInformations(String reservationsId) {
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String customerTablesd = "";
        if(abbreviationList.size()!=0){
            for (int i = 0; i < abbreviationList.size(); i++) {
                String head = abbreviationList.get(i);
                if (i == abbreviationList.size() - 1) {
                    customerTablesd += "select * from " + head + "customer_info t where reservations_id='" + reservationsId + "' and arrival_time is not null";
                } else {
                    customerTablesd += "select * " +
                            "from " + head + "customer_info t where reservations_id='" + reservationsId + "' " +
                            "and arrival_time is not null  union ";
                }
            }
            CustomerInfo customerInfo = this.selectOneByReservationsIdFirsts(reservationsId, customerTablesd);  //首次到店患者信息
            return customerInfo;
        }else{
            return null;
        }
    }
    public CustomerInfo selectOneByReservationsIdFirsts(String reservationsId, String customerTablesd) {
        String sql = "select * from (" + customerTablesd + ")  t order by t.arrival_time asc limit 1;";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        return customerInfo;
    }

    public int regresses(String customerId,String abbreviation,String outpatientName,String userAccount,String userId) {
        String selectiscom = "select id from " + abbreviation+"customer_info " +
                "where id='" + customerId + "' and left(start_time,10)>left(now(),10)";
        List<CustomerInfo> iscomList = sqlMapper.selectList(selectiscom, CustomerInfo.class);
        if (iscomList.size() > 0) {
            return 1;
        } else {
            String selsql = "select id,a.arrival_status,(case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中' " +
                    "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话' " +
                    "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName," +
                    "a.* from  " + abbreviation+"customer_info a where id = '" + customerId + "'";
            CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
            String customerStatus = this.getNewCustomerStatus(customerInfo.getReservationsId(),abbreviation);
            String sql = "";
            String selectsql1 = "select id,status,follower,archiving,belonger,szm,first_visit_date from reservations_info a where a.id='" + customerInfo.getReservationsId() + "' limit 0,1";
            ReservationsInfo reservationsInfo = sqlMapper.selectOne(selectsql1, ReservationsInfo.class);
            String medicalRecordNumber = "";
            String scarchiving = "";
            String tableName ="";
            if (reservationsInfo != null) {
                if (StringUtil.isNotEmpty(reservationsInfo.getFollower())) {
                    scarchiving = reservationsInfo.getFollower();
                } else {
                    scarchiving = reservationsInfo.getArchiving();
                }
                if ("预约".equals(reservationsInfo.getStatus())) {
                    medicalRecordNumber = this.getMedicalRecordNumbers(customerInfo.getCustomerPhone(),abbreviation);
                    String sql1 = "update reservations_info a set a.status='到店', a.arrival_store='" + outpatientName + "'," +
                            "a.belonger='" + scarchiving + "', a.identification_number='" + medicalRecordNumber + "',a.first_visit_date='" + DateTimeKit.now() + "'" +
                            " where id='" + customerInfo.getReservationsId() + "'";
                    sqlMapper.update(sql1);
                    String sql2 = "update reservations_infosc a set a.status='到店', a.arrival_store='" + outpatientName + "'," +
                            "a.belonger='" + scarchiving + "', a.identification_number='" + medicalRecordNumber + "',a.first_visit_date='" + DateTimeKit.now() + "'" +
                            " where id='" + customerInfo.getReservationsId() + "'";
                    sqlMapper.update(sql2);
                }
            } else {
                medicalRecordNumber =this.getMedicalRecordNumbers(customerInfo.getCustomerPhone(), abbreviation);
                customerInfo.setIdentificationNumber(medicalRecordNumber);
                this.insert(customerInfo, outpatientName, userAccount);
            }
            String selectsql2 = "select * from distinction_customer_status " +
                    " where reservations_id='" + customerInfo.getReservationsId() + "' " +
                    "and arrival_store='" + outpatientName + "' and status=1 ";
            List<CustomerInfo> cInfo2 = sqlMapper.selectList(selectsql2, CustomerInfo.class);
            if (cInfo2.size() == 0) {  //第一次来本店
                String sqld = "select id,abbreviation from distinction_customer_status " +
                        " where reservations_id ='" + customerInfo.getReservationsId() + "' and status=1 order by add_time desc limit 0,1 ";
                Dept infod = sqlMapper.selectOne(sqld, Dept.class);
                if (infod != null) {   //没来过本店，去过另一家店
                    tableName = infod.getAbbreviation() + "customer_info";
                    String sqld1 = "select id as id,medical_record_number as medicalRecordNumber from " + tableName + " where reservations_id ='" + customerInfo.getReservationsId() + "' order by arrival_time desc limit 0,1 ";
                    CustomerInfo infod1 = sqlMapper.selectOne(sqld1, CustomerInfo.class);
                    medicalRecordNumber = infod1.getMedicalRecordNumber();
                } else if ("".equals(medicalRecordNumber)) {
                    medicalRecordNumber = this.getMedicalRecordNumbers(customerInfo.getCustomerPhone(), abbreviation);
                }
                String sqld2 = "delete from distinction_customer_status " +
                        " where reservations_id='" + customerInfo.getReservationsId() + "' " +
                        "and arrival_store='" + outpatientName + "' and status=2 ";
                sqlMapper.delete(sqld2);
                String sql1 = "insert into distinction_customer_status (reservations_id,customer_name,customer_phone,arrival_store,abbreviation,deal_status,add_time,update_time,medical_record_number) " +
                        " values('" + customerInfo.getReservationsId() + "','" + customerInfo.getCustomerName() + "','" + customerInfo.getCustomerPhone()
                        + "','" + outpatientName + "','" + abbreviation + "','未成交','"
                        + DateTimeKit.now() + "','" + DateTimeKit.now() + "','" + medicalRecordNumber + "') ";
                sqlMapper.insert(sql1);
                sql = "update " + abbreviation+"customer_info  set arrival_status ='2',medical_record_number='" + medicalRecordNumber + "'," +
                        "scarchiving='" + scarchiving + "',status='到店',szm=firstconcats('" + customerInfo.getCustomerName() + "') ,customer_status='" + customerStatus + "'," +
                        "arrival_time='" + DateTimeKit.now() + "',first_visit_date='" + DateTimeKit.now() + "' where id = '" + customerId + "' ";
            } else {  //复诊
                if ("新客首次".equals(customerStatus)) {
                    customerStatus = "新客首次";
                } else if ("新客多次".equals(customerStatus)) {
                    customerStatus = "新客多次";
                } else if ("新客N次".equals(customerStatus)) {
                    customerStatus = "新客N次";
                } else {
                    customerStatus = "复诊";
                }
                String sqlgetm = "select id,medical_record_number,phone_shar_ship from " + abbreviation+"customer_info " +
                        " where customer_phone='" + customerInfo.getCustomerPhone() + "' and reservations_id='" + customerInfo.getReservationsId() + "'" +
                        " and left(arrival_time,10)<'" + DateTimeKit.now().substring(0, 10) + "' limit 0,1";
                CustomerInfo customerInfo2 = sqlMapper.selectOne(sqlgetm, CustomerInfo.class);
                if (customerInfo2 != null) {
                    sql = "update " + abbreviation+"customer_info  set arrival_status ='2',status='到店',phone_shar_ship='" + customerInfo2.getPhoneSharShip() + "'," +
                            "medical_record_number='" + customerInfo2.getMedicalRecordNumber() + "',status='到店',customer_status='" + customerStatus + "'," +
                            "arrival_time='" + DateTimeKit.now() + "' where id = '" + customerId + "' ";
                } else {
                    String sqlgetm2 = "select id,medical_record_number,phone_shar_ship from "+ abbreviation+"customer_info " +
                            " where customer_name='" + customerInfo.getCustomerName() + "' and reservations_id='" + customerInfo.getReservationsId() + "' limit 0,1";
                    CustomerInfo customerInfo22 = sqlMapper.selectOne(sqlgetm2, CustomerInfo.class);
                    sql = "update "+ abbreviation+"customer_info   set arrival_status ='2',status='到店',phone_shar_ship='" + customerInfo22.getPhoneSharShip() + "'," +
                            "scarchiving='" + scarchiving + "',medical_record_number='" + customerInfo22.getMedicalRecordNumber() + "',status='到店',customer_status='" + customerStatus + "'," +
                            "arrival_time='" + DateTimeKit.now() + "' where id = '" + customerId + "' ";
                }
            }
            //根据预约ID 查询最近一次到店时间的患者
            CustomerInfo info = this.firstVisitInformations(customerInfo.getReservationsId()); //首次到店的患者信息
            if (info != null) {
                if (info.getReferralerPerson() == null || info.getReferralerPerson() == "" || info.getReferralerPersoned() == null || info.getReferralerPersoned() == "" || info.getReferralerTime() == null || info.getReferralerTime() == "" || info.getReferralerRemarks() == null || info.getReferralerRemarks() == "") {
                    info.setReferralerPerson("null");
                    info.setReferralerPersoned("null");
                    info.setReferralerRemarks("null");
                    info.setReferralerTime(null);
                } else {
                    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                    Date dt1 = null;
                    Date dt2 = null;
                    try {
                        dt1 = df.parse(DateTimeKit.now().substring(0, 10));
                        dt2 = df.parse(info.getReferralerTime().substring(0, 10));
                        if (dt1.getTime() - dt2.getTime() > 30 * 24 * 60 * 60 * 1000L) {
                            info.setReferralerPerson("null");
                            info.setReferralerPersoned("null");
                            info.setReferralerRemarks("null");
                            info.setReferralerTime(null);
                        } else {
                            info.setReferralerPerson(info.getReferralerPerson());
                            info.setReferralerPersoned(info.getReferralerPersoned());
                            info.setReferralerTime(info.getReferralerTime());
                            info.setReferralerRemarks(info.getReferralerRemarks());
                        }
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                }
                String referralerPerson = "";
                String referralerPersoned = "";
                String referralerTime = "";
                String referralerRemarks = "";
                String marketFilingTime = "";
                if (reservationsInfo.getFirstVisitDate() == null || reservationsInfo.getFirstVisitDate() == "") {
                    marketFilingTime = "null";
                } else {
                    marketFilingTime = "'" + customerInfo.getFirstVisitDate() + "'";
                }
                if (StringUtil.isNotEmpty(info.getReferralerPerson())) {
                    referralerPerson = "'" + info.getReferralerPerson() + "'";
                } else {
                    referralerPerson = "null";
                }
                if (StringUtil.isNotEmpty(info.getReferralerPersoned())) {
                    referralerPersoned = "'" + info.getReferralerPersoned() + "'";
                } else {
                    referralerPersoned = "null";
                }
                if (StringUtil.isNotEmpty(info.getReferralerTime())) {
                    referralerTime = "'" + info.getReferralerTime() + "'";
                } else {
                    referralerTime = null;
                }
                if (StringUtil.isNotEmpty(info.getReferralerRemarks())) {
                    referralerRemarks = "'" + info.getReferralerRemarks() + "'";
                } else {
                    referralerRemarks = "null";
                }
                String updateSql = "update " + abbreviation+"customer_info a set a.referraler_person=" + referralerPerson + ",a.referraler_personed=" + referralerPersoned + "," +
                        " a.referraler_time=" + referralerTime + ",a.referraler_remarks=" + referralerRemarks + ",market_filing_time="+marketFilingTime+" where id='" + customerId + "' ";
                sqlMapper.update(updateSql);
            }
            //插入视图改动日志 点击挂号
            String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                    " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" + userAccount + "','修改预约','" + userId + "','挂号', '"
                    + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                    "','预约状态：" + customerInfo.getArrivalStatusName() + "->挂号','" + customerId + "','" + customerInfo.getReservationsId() + "');";
            sqlMapper.insert(sq11);
            sqlMapper.update(sql);
         //   webSocket.sendMessageViews("挂号", ShiroKit.getUser().getAbbreviation());
            return 0;
        }
    }

    public int startTreatment(String customerId, String abbreviation, String userId, String userAccount, String outpatientName) {
        String selsql = "select id, (case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中' " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话' " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName,a.customer_name,a.customer_phone,a.customer_status," +
                "a.reservations_id,a.arrival_status from  " + abbreviation+"customer_info a where id = '" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        if (("1").equals(customerInfo.getArrivalStatus()) || ("6").equals(customerInfo.getArrivalStatus())) { //未挂号患者医生点了接诊
            this.regresses(customerId, abbreviation, outpatientName, userAccount, userId); //先走挂号方法
        }
        String sql = "update " + abbreviation+"customer_info  set arrival_status ='3' where id = '" + customerId + "'";
        //插入视图改动日志 开始治疗
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userAccount + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                "','预约状态：" + customerInfo.getArrivalStatusName() + "->开始治疗','" + customerId + "');";
        sqlMapper.update(sql);
     //   webSocket.sendMessageViews("开始治疗", ShiroKit.getUser().getAbbreviation());
        return sqlMapper.insert(sq11);
    }

    public int completionTreatment(String customerId,String abbreviation,String userId,String userAccount,String outpatientName) {
        String sql = "update " + abbreviation+"customer_info  set arrival_status ='4' where id = '" + customerId + "'";
        String selsql = "select id, (case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中' " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话' " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName,a.customer_name,a.customer_phone,a.customer_status," +
                "a.reservations_id from  " + abbreviation+"customer_info a where id = '" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        //插入视图改动日志 完成治疗
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userAccount + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                "','预约状态：" + customerInfo.getArrivalStatusName() + "->完成治疗','" + customerId + "');";
        return sqlMapper.insert(sq11);
    }

    public String returnTo(String customerId, String abbreviation) {
        String sql = "update " + abbreviation+"customer_info set arrival_status = arrival_status-1 where id = '" + customerId + "'";
        int a = sqlMapper.update(sql);
        String sql2 = "select id,arrival_status from " + abbreviation+"customer_info where id= '" + customerId + "'";
        ConsultantInfo customerInfo = sqlMapper.selectOne(sql2, ConsultantInfo.class);
        return a + "," + customerInfo.getArrivalStatus();
    }
    public int cancelAppointmentok(String customerId, String remarks, String abbreviation, String userId, String userAccount, String outpatientName) {
        String sql = "update " + abbreviation+"customer_info set arrival_status='8',status='取消预约',remarks='" + remarks + "' where id = '" + customerId + "'";
        String selsql = "select id, a.customer_name,a.customer_phone,a.reservations_id from  " + abbreviation+"customer_info a where id = '" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        //插入视图改动日志 取消预约
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,remarks) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userAccount + "','取消预约','" + userId + "','取消预约', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                "','预约状态：预约->取消预约','" + customerId + "','" + remarks + "');";
        sqlMapper.insert(sq11);
        return sqlMapper.update(sql);
    }

    public int alreadyLeave(String customerId, String remarks, String abbreviation, String userId, String userAccount, String outpatientName) {
        String sql = "update " + abbreviation+"customer_info  set arrival_status ='5' where id = '" + customerId + "'";
        String selsql = "select id, (case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中' " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话' " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName,a.customer_name,a.customer_phone,a.customer_status," +
                "a.reservations_id from  " + abbreviation+"customer_info a where id = '" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        //插入视图改动日志 已离开
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userAccount + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                "','预约状态：" + customerInfo.getArrivalStatusName() + "->已离开','" + customerId + "','" + customerInfo.getReservationsId() + "');";
        sqlMapper.insert(sq11);
        return sqlMapper.update(sql);
    }

    public int sureAppointment(String customerId, String abbreviation, String userId, String userAccount, String outpatientName) {
        String selsql = "select * from  " + abbreviation+"customer_info where id = '" + customerId + "'";
        CustomerInfo customerIn = sqlMapper.selectOne(selsql, CustomerInfo.class);
        String sql = " ";
        if (("9").equals(customerIn.getArrivalStatus()) || ("10").equals(customerIn.getArrivalStatus())) {
            sql = "update " + abbreviation+"customer_info  set arrival_status ='10' where id = '" + customerId + "'";
        } else {
            sql = "update " + abbreviation+"customer_info  set arrival_status ='6' where id = '" + customerId + "'";
        }
        //插入视图改动日志 预约确定
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userAccount + "','修改预约','" + userId + "','预约', '"
                + customerIn.getCustomerName() + "', '" + customerIn.getCustomerPhone() + "','成功','" + outpatientName +
                "','预约状态：预约->确定','" + customerId + "');";
        sqlMapper.insert(sq11);
     //   webSocket.sendMessageViews("确定", abbreviation);
        return sqlMapper.update(sql);
    }

    public int no_sureAppointment(String customerId, String abbreviation, String userId, String userAccount, String outpatientName) {
        String sql = "update " + abbreviation+"customer_info  set arrival_status ='1' where id = '" + customerId + "'";
        String selsql = "select customer_status,customer_name,Customer_phone from  " + abbreviation+"customer_info where id = '" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        //插入视图改动日志 取消确定
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userAccount + "','修改预约','" + userId + "','预约', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                "','预约状态：确定->取消确定','" + customerId + "');";
        sqlMapper.insert(sq11);
        return sqlMapper.update(sql);
    }

    public int fallbackAction(String customerId, String arrivalStatus, String abbreviation, String userId, String userAccount, String outpatientName) {
        String selsql = "select id,a.arrival_status,(case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中' " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话' " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName,a.customer_name,a.customer_phone,a.customer_status," +
                "a.reservations_id from  " + abbreviation+"customer_info a where id = '" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        if (arrivalStatus.equals("5")) {
            String sql = "update " + abbreviation+"customer_info  set arrival_status ='4' where id = '" + customerId + "'";
            sqlMapper.update(sql);
            //插入视图改动日志 回退
            String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                    " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" + userAccount + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                    + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                    "','预约状态：离开->完成治疗','" + customerId + "','" + customerInfo.getReservationsId() + "');";
            sqlMapper.insert(sq11);
        } else if (arrivalStatus.equals("4")) {
            String sql = "update " + abbreviation+"customer_info  set arrival_status ='3' where id = '" + customerId + "'";
            //插入视图改动日志 回退
            String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                    " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" + userAccount + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                    + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                    "','预约状态：完成治疗->就诊中','" + customerId + "','" + customerInfo.getReservationsId() + "');";
            sqlMapper.update(sql);
            sqlMapper.insert(sq11);
        } else if (arrivalStatus.equals("3")) {
            String sql = "update " + abbreviation+"customer_info  set arrival_status ='2' where id = '" + customerId + "'";
            //插入视图改动日志 回退
            String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                    " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" + userAccount + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                    + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                    "','预约状态：就诊中->到店','" + customerId + "','" + customerInfo.getReservationsId() + "');";
            sqlMapper.update(sql);
            sqlMapper.insert(sq11);
        } else if (arrivalStatus.equals("7")) {
            String sql = "update " + abbreviation+"customer_info  set arrival_status ='1' where id = '" + customerId + "'";
            //插入视图改动日志 回退
            String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                    " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" + userAccount + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                    + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                    "','预约状态：失约->预约','" + customerId + "','" + customerInfo.getReservationsId() + "');";
            sqlMapper.insert(sq11);
            sqlMapper.update(sql);
        } else if (arrivalStatus.equals("2")) {
            String sql = "update " + abbreviation+"customer_info  set arrival_status ='1',status ='预约',arrival_time=null,customer_status =null where id = '" + customerId + "'";
            //插入视图改动日志 回退
            String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                    " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" + userAccount + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                    + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                    "','预约状态：到店->预约','" + customerId + "','" + customerInfo.getReservationsId() + "');";
            sqlMapper.update(sql);
            sqlMapper.insert(sq11);
            String sql2 = "select id from reservations_info where id='" + customerInfo.getReservationsId() + "' " +
                    "and left(first_visit_date,10) = '" + (DateTimeKit.now()).substring(0, 10) + "' and one_channel!='直接上门' and one_channel!='老带新' limit 0,1";
            ReservationsInfo reservationsInfo = sqlMapper.selectOne(sql2, ReservationsInfo.class);
            if (reservationsInfo != null) {
                String updatesql = "update reservations_info set status='预约',first_visit_date=null where id='" + customerInfo.getReservationsId() + "'";
                sqlMapper.update(updatesql);
                String delsql = "delete from distinction_customer_status where reservations_id='" + customerInfo.getReservationsId() + "'" +
                        " and arrival_store='" + outpatientName + "' and status=1 ";
                sqlMapper.delete(delsql);
            } else {
                String sql3 = "select id from reservations_info where id='" + customerInfo.getReservationsId() + "' " +
                        "and left(first_visit_date,10) = '" + (DateTimeKit.now()).substring(0, 10) + "' and (one_channel='直接上门' or one_channel='老带新') limit 0,1";
                ReservationsInfo reservationsInfo1 = sqlMapper.selectOne(sql3, ReservationsInfo.class);
                if (reservationsInfo1 != null) {
                    String deletesql = "delete from reservations_info where id='" + customerInfo.getReservationsId() + "'";
                    sqlMapper.delete(deletesql);
                    String delsql = "delete from distinction_customer_status where reservations_id='" + customerInfo.getReservationsId() + "'" +
                            " and arrival_store='" + outpatientName + "' and status=1 ";
                    sqlMapper.delete(delsql);
                }
            }
        } else if (arrivalStatus.equals("1")) {
            String sql = "update " + abbreviation+"customer_info  set arrival_status ='1' where id = '" + customerId + "'";
            sqlMapper.update(sql);
        }
      //  webSocket.sendMessageViews("回退", abbreviation);
        return 1;
    }

    public int addDoctorContent(String customerId, String doctor, String revisitTime, String remarks, String reservationsId, String arrivalStore, String outpatientName) {
        String sql = "insert into return_doctor_visit_info (reservations_id,customer_id,revisit,revisit_time," +
                "outpatient_name,doctor,remarks,create_time) values ('" + reservationsId
                + "','" + customerId + "','" + doctor + "','" + revisitTime + "','" + outpatientName
                + "','" + doctor + "','" + remarks + "','" + DateTimeKit.now() + "')";
        return sqlMapper.insert(sql);
    }

    public int addDoctorContent2(String customerId, String doctor, String revisitContent, String revisitTime,
                                 String remarks, String reservationsId, String arrivalStore, String createName, String revisit,String userAccount,String userId,String outpatientName,String abbreviation) {
        String sql = "insert into return_doctor_visit_info (reservations_id,customer_id,revisit,revisit_time," +
                "outpatient_name,doctor,remarks,revisit_content,create_name,revisit_status,create_time) values ('" + reservationsId
                + "','" + customerId + "','" + revisit + "','" + revisitTime + "','" + outpatientName
                + "','" + doctor + "','" + remarks + "','" + revisitContent + "','" + createName + "','" + 1 + "','" + DateTimeKit.now() + "')";
        sqlMapper.insert(sql);
        String insertInto = "update " + abbreviation+"customer_info a set a.visit_status='1',a.first_return_settings='1' where id='" + customerId + "'";
        //插入随访日志
        String sq11 = "INSERT INTO `doctor_visit_info_log` (createtime,single_person,logtype, `userid`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userAccount + "','新增随访','" + userId + "','成功','" + outpatientName +
                "','随访时间：" + revisitTime + " </br>\n随访医生：" + doctor + " </br>\n随访人员：" + revisit + "</br>\n创建人:" + createName + "</br>\n创建内容：" + remarks + " " +
                "</br>\n随访结果：" + revisitContent + "</br>\n随访状态：待计划 </br>\n','" + customerId + "','" + reservationsId + "');";
        sqlMapper.insert(sq11);
        return sqlMapper.update(insertInto);
    }

    public int addDoctorContents(String customerId, String doctor, String revisitTime, String remarks, String reservationsId, String arrivalStore, String createName, String revisit, String outpatientName,String abbreviation) {
        String sql = "insert into return_doctor_visit_info (reservations_id,customer_id,revisit,revisit_time," +
                "outpatient_name,doctor,remarks,create_name,revisit_status,create_time) values ('" + reservationsId
                + "','" + customerId + "','" + revisit + "','" + revisitTime + "','" + outpatientName
                + "','" + doctor + "','" + remarks + "','" + createName + "','" + 1 + "','" + DateTimeKit.now() + "')";
        sqlMapper.insert(sql);
        String insertInto = "update " + abbreviation+"customer_info a set a.visit_status='1' where id='" + customerId + "'";
        return sqlMapper.update(insertInto);
    }

    public int addDoctorContent3(String customerId, String doctor, String revisitContent, String revisitTime,
                                 String remarks, String reservationsId, String arrivalStore, String createName, String revisit,String userAccount,String userId,String outpatientName,String abbreviation) {
        int revisitStatus = 2;
        String sql = "insert into return_doctor_visit_info (reservations_id,customer_id,revisit,revisit_time," +
                "outpatient_name,doctor,remarks,revisit_content,create_name,revisit_status,create_time) values ('" + reservationsId
                + "','" + customerId + "','" + revisit + "','" + revisitTime + "','" + outpatientName
                + "','" + doctor + "','" + remarks + "','" + revisitContent + "','" + createName
                + "','" + revisitStatus + "','" + DateTimeKit.now() + "')";
        sqlMapper.insert(sql);
        String insertInto = "update " + abbreviation+"customer_info a set a.visit_status='1',a.first_return_settings='1' where id='" + customerId + "'";
        //插入随访日志
        String sq11 = "INSERT INTO `doctor_visit_info_log` (createtime,single_person,logtype, `userid`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userAccount + "','新增随访','" + userId + "','成功','" + outpatientName +
                "','随访时间：" + revisitTime + " </br>\n随访医生：" + doctor + " </br>\n随访人员：" + revisit + "</br>\n创建人:" + createName + "</br>\n创建内容：" + remarks + " " +
                "</br>\n随访结果：" + revisitContent + "</br>\n随访状态：随访完成 </br>\n','" + customerId + "','" + reservationsId + "');";
        sqlMapper.insert(sq11);
        return sqlMapper.update(insertInto);
    }

    public int addDoctorContent4(String customerId, String doctor, String revisitContent, String revisitTime, String remarks, String reservationsId, String arrivalStore, String createName, String revisit, String outpatientName,String abbreviation) {
        int revisitStatus = 3;
        String sql = "insert into return_doctor_visit_info (reservations_id,customer_id,revisit,revisit_time," +
                "outpatient_name,doctor,remarks,revisit_content,create_name,revisit_status,create_time) values ('" + reservationsId
                + "','" + customerId + "','" + revisit + "','" + revisitTime + "','" + outpatientName
                + "','" + doctor + "','" + remarks + "','" + revisitContent + "','" + createName
                + "','" + revisitStatus + "','" + DateTimeKit.now() + "')";
        sqlMapper.insert(sql);
        String insertInto = "update " + abbreviation+"customer_info a set a.visit_status='1',a.first_return_settings='1' where id='" + customerId + "'";
        return sqlMapper.update(insertInto);
    }

    public int addDoctorContent5(String customerId, String doctor, String revisitContent, String revisitTime, String remarks, String reservationsId, String arrivalStore, String createName, String revisit, String outpatientName,String abbreviation) {
        int revisitStatus = 4;
        String sql = "insert into return_doctor_visit_info (reservations_id,customer_id,revisit,revisit_time," +
                "outpatient_name,doctor,remarks,revisit_content,create_name,revisit_status,create_time) values ('" + reservationsId
                + "','" + customerId + "','" + revisit + "','" + revisitTime + "','" + outpatientName
                + "','" + doctor + "','" + remarks + "','" + revisitContent + "','" + createName
                + "','" + revisitStatus + "','" + DateTimeKit.now() + "')";
        sqlMapper.insert(sql);
        String insertInto = "update " + abbreviation+"customer_info a set a.visit_status='1',a.first_return_settings='1' where id='" + customerId + "'";
        return sqlMapper.update(insertInto);
    }

    public int updateDoctorContent(String doctor, String createName, String revisitTime, String revisitContent, String doctorVisitId, String remarks, String customerId, String reservationsId, String revisit, String userAccount, String userId, String outpatientName, String abbreviation) {
        String updatesql = "update " + abbreviation+"customer_info set arrival_status=6 where id='" + customerId + "' and (arrival_status=1 or arrival_status=7);";
        String sql = "update return_doctor_visit_info set doctor='" + doctor + "',create_name='" + createName
                + "',revisit_time='" + revisitTime + "',revisit_content='" + revisitContent
                + "',remarks='" + remarks + "',revisit='" + revisit + "' where reservations_id='" + reservationsId + "' and id='" + doctorVisitId + "'";
        sqlMapper.update(updatesql);
        //插入随访日志
        String sq11 = "INSERT INTO `doctor_visit_info_log` (createtime,single_person,logtype, `userid`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userAccount + "','修改随访','" + userId + "','成功','" + outpatientName +
                "','随访结果：" + revisitContent + "</br>\n随访状态：待计划->待计划</br>\n','" + customerId + "','" + reservationsId + "');";
        sqlMapper.insert(sq11);
        return sqlMapper.update(sql);
    }

    public int updateDoctorContent1(String doctor, String createName, String revisitTime, String revisitContent, String doctorVisitId, String remarks, String reservationsId, String customerId, String revisit, String userAccount, String userId, String outpatientName) {
        String sql = "update return_doctor_visit_info set doctor='" + doctor + "',create_name='" + createName
                + "',revisit_time='" + revisitTime + "',revisit_content='" + revisitContent
                + "',remarks='" + remarks + "',revisit='" + revisit + "',revisit_status='2' where reservations_id='" + reservationsId + "' and id='" + doctorVisitId + "'";
        //插入随访日志
        String sq11 = "INSERT INTO `doctor_visit_info_log` (createtime,single_person,logtype, `userid`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userAccount + "','修改随访','" + userId + "','成功','" + outpatientName +
                "','随访结果：" + revisitContent + "</br>\n随访状态：待计划->回访完成</br>\n','" + customerId + "','" + reservationsId + "');";
        sqlMapper.insert(sq11);
        return sqlMapper.update(sql);
    }

    public int updateDoctorContent2(String doctor, String createName, String revisitTime, String revisitContent, String doctorVisitId, String remarks, String reservationsId, String revisit) {
        String sql = "update return_doctor_visit_info set doctor='" + doctor + "',create_name='" + createName
                + "',revisit_time='" + revisitTime + "',revisit_content='" + revisitContent
                + "',remarks='" + remarks + "',revisit='" + revisit + "',revisit_status='3' where reservations_id='" + reservationsId + "' and id='" + doctorVisitId + "'";
        return sqlMapper.update(sql);
    }

    public List<DoctorDiagnosisInfo> diagnoseList(String reservationsId) {
        //查询此人的看诊记录
        String sql = "select customer_id as customerId,reservations_id as reservationsId," +
                "outpatient_name as outpatientName,diagnose_time as diagnoseTime " +
                " from doctor_diagnosis_info where reservations_id='" + reservationsId + "'";
        List<DoctorDiagnosisInfo> doctorDiagnosisInfo = sqlMapper.selectList(sql, DoctorDiagnosisInfo.class);
        return doctorDiagnosisInfo;
    }

    public String uploadIcon(MultipartFile picture, String cri, String outpatientName, String abbreviation, String userAccount, String userId) {
        String payServiceId = cri.split(",")[0];
        if ("aa".equals(payServiceId)) {
            payServiceId = "";
        }
        String customerId = cri.split(",")[1];
        String reservationsId = cri.split(",")[2];
        String iconType = cri.split(",")[3];
        String remarks = cri.split(",")[4];
        if ("aa".equals(remarks)) {
            remarks = "";
        }
        String alldiagnosetime = cri.split(",")[5];
        String alldiagnose2 = alldiagnosetime + " " + outpatientName;
//        String realPath = "D:\\8068\\apache-tomcat-8.0.30\\webapps\\ROOT\\yingxiang";
//        String httpUrl = "http://118.123.15.60:8068/yingxiang/";
        //避免出现相同的图片名称  ids
        String ids = CommUtils.getUUID();
        String imageurl = "yingxiang/" + ids + (picture.getOriginalFilename()).substring(1, 5) + ".png";
        try {
//            FileUtils.copyInputStreamToFile(picture.getInputStream(), new File(realPath, ids + (picture.getOriginalFilename()).substring(1, 5) + ".png"));
            aliyunOssUtil.pushSingleFile(picture, imageurl);
            String sql = "insert into  icon_info (customer_id,reservations_id,outpatient_name,add_name," +
                    "add_time,image_url,icon_name,remarks,diagnoseTime,pay_service_id) values ('" + customerId
                    + "','" + reservationsId + "','" + outpatientName + "','"
                    + userAccount + "','" + DateTimeKit.now() + "','" + MemberEnumConfig.ossphoeurl + imageurl + "','" + iconType + "','" + remarks + "','" + alldiagnose2 + "','" + payServiceId + "')";
            sqlMapper.insert(sql);
            //插入影像新增日志
            String selectSql2 = "select * from icon_info t where customer_id='" + customerId + "' order by add_time limit 1";
            IconInfo iconInfo = sqlMapper.selectOne(selectSql2, IconInfo.class);
            String customerSql = "select id,customer_name,customer_phone from " + abbreviation+"customer_info  where id='" + customerId + "' limit 1";
            CustomerInfo customerInfo = sqlMapper.selectOne(customerSql, CustomerInfo.class);
            String inertsq1 = "INSERT INTO `icon_info_log` (createtime,reservations_id,icon_id,single_person,logtype, `userid`, `customer_name`,`customer_phone`," +
                    " `succeed`,outpatient_name ,`message`,customer_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" + iconInfo.getReservationsId() + "','" + iconInfo.getId() + "','" + userAccount + "','新增影像','" + userId + "', '"
                    + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                    "','" + userAccount + " 把 " + customerInfo.getCustomerName() + " 的影像在:" + DateTimeKit.now() + "进行上传','" + customerId + "');";
            sqlMapper.insert(inertsq1);//插入删除日志
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return MemberEnumConfig.ossphoeurl + imageurl;
    }

    public List<IconInfo> getIconInfo(String cri, String areaAbbreviation) {
        List<IconInfo> list = new ArrayList<>();
        String reservationsId = cri.split(",")[1];
        String iconType = cri.split(",")[2];
        String param = "";
        if (StringUtil.isNotEmpty(iconType) && !iconType.equals("所有类型")) {
            param += " and i.icon_name = '" + iconType + "' ";
        }
        if (StringUtil.isNotEmpty(reservationsId)) {
            param += " and i.reservations_id = '" + reservationsId + "' ";
        }
        try {
            String sql = "  select  i.id ,i.customer_id as customerId,i.reservations_id as reservationsId,i.icon_name as iconName," +
                    "  i.outpatient_name as outpatientName,i.diagnoseTime as diagnoseTime,i.add_name as addName,i.add_time as addTime," +
                    "i.remarks as remarks,i.image_url as imageUrl,p.pay_service_name as payServiceName  from icon_info i " +
                    "left join " + areaAbbreviation+"pay_service_info p on p.id=i.pay_service_id  where 1=1 " + param + " order by diagnoseTime DESC";
            list = sqlMapper.selectList(sql, IconInfo.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public String uploadIcons(MultipartFile picture, String abbreviation, String outpatientName, String userAccount,  String cri) {
        String customerId = cri.split(",")[0];
        String reservationsId = cri.split(",")[1];
        String iconType = cri.split(",")[2];
        String num = cri.split(",")[3];
        String iconName1 = "";
        String originalFile2 = "";
        if ("0".equals(iconType)) {
            iconType = "术前";
            iconName1 = "3";
        }
        if ("1".equals(iconType)) {
            iconType = "术中";
            iconName1 = "2";
        }
        if ("2".equals(iconType)) {
            iconType = "术后";
            iconName1 = "1";
        }
        String sql1 = "select * from " + abbreviation+"customer_info where id= '" + customerId + "'";
        ConsultantInfo info1 = sqlMapper.selectOne(sql1, ConsultantInfo.class);
        String originalFile1 = "JPG_" + DateTimeKit.now().substring(0, 10);
        if (info1.getCustomerPhone().indexOf("+") != -1) {
            originalFile2 = info1.getCustomerName() + (info1.getCustomerPhone().substring(0, info1.getCustomerPhone().indexOf("+")));
        } else {
            originalFile2 = info1.getCustomerName() + info1.getCustomerPhone();
        }
        String originalFileName = info1.getCustomerName() + "-" + iconType + ".jpg";
        String realPath = "D:\\8068\\apache-tomcat-8.0.30\\webapps\\ROOT\\photo\\" + abbreviation + "\\" + originalFile1 + "\\" + originalFile2;
//        String httpUrl = "http://118.123.15.60:8068/photo/" + abbreviation + "/" + originalFile1 + "/" + originalFile2 + "/";
        String httpUrl = "photo/" + abbreviation + "/" + originalFile1 + "/" + originalFile2 + "/";
        String imageurl = httpUrl + "\\" + originalFileName;
        String httpUrl2 = "photo/" + abbreviation + "/" + originalFile1 + "/" + originalFile2 + "/" + originalFileName;
        try {
            OSSClient ossClient = new OSSClient(MemberEnumConfig.endPoint, MemberEnumConfig.accessKeyId, MemberEnumConfig.accessKeySecret);
            // 文件夹名OSSClient
            final String keySuffixWithSlash = httpUrl2;
            // 判断文件夹是否存在，不存在则创建
            if (!ossClient.doesObjectExist(MemberEnumConfig.backetName, keySuffixWithSlash)) {
                // 创建文件夹
                ossClient.putObject(MemberEnumConfig.backetName, keySuffixWithSlash, new ByteArrayInputStream(new byte[0]));
            }
            // 得到文件夹名
            OSSObject object = ossClient.getObject(MemberEnumConfig.backetName, keySuffixWithSlash);
            String fileDir = object.getKey();
            ossClient.shutdown();
            aliyunOssUtil.pushSingleFile(picture, httpUrl2);
//            FileUtils.copyInputStreamToFile(picture.getInputStream(), new File(realPath, originalFileName));
            String sql = "insert into icon_infos1 (reservations_id,customer_id,outpatient_name,abbreviation,add_name," +
                    "add_time,image_url,icon_name,icon_name1,num) values ('" + reservationsId + "','" + customerId
                    + "','" + outpatientName + "','" + abbreviation + "','"
                    + userAccount + "','" + DateTimeKit.now() + "','" + MemberEnumConfig.ossphoeurl + imageurl + "','" + iconType + "','" + iconName1 + "','" + num + "')";
            sqlMapper.insert(sql);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return realPath;
    }

    public String submissionUploadIcons(String cri, String abbreviation, String outpatientName, String userAccount) {
        String payServiceId = cri.split(",")[0];
        String customerId = cri.split(",")[1];
        String reservationsId = cri.split(",")[2];
        String remarks = cri.split(",")[3];
        String alldiagnosetime = cri.split(",")[4];
        String doctor = cri.split(",")[5];
        String toothPosition = cri.split(",")[6];
        String iconType1 = cri.split(",")[7];
        String iconType2 = cri.split(",")[8];
        String iconType3 = cri.split(",")[9];
        String originalFileName;
        String yoriginalFileName;
        String originalFile2;
        String originalFile3;
        String realNewPath;
        String realPath;
        String httpUrl;
        String imageurl;
        String imageurls;
        if ("payServiceId".equals(payServiceId)) {
            payServiceId = "";
        }
        if ("remarks".equals(remarks)) {
            remarks = "";
        }
        if ("doctor".equals(remarks)) {
            doctor = "";
        }
        String randomNumber = "select DATE_FORMAT(now(),'%Y%m%d%k%i%S') as randomNumber from dual";
        DaulInfo infos = sqlMapper.selectOne(randomNumber, DaulInfo.class);
        String sql1 = "select * from " + abbreviation+"customer_info where id= '" + customerId + "'";
        ConsultantInfo info1 = sqlMapper.selectOne(sql1, ConsultantInfo.class);
        //String alldiagnose2 = alldiagnosetime + " " + ShiroKit.getUser().getOutpatientName();
        String time = DateTimeKit.now();
        String originalFile1 = "JPG_" + time.substring(0, 10);
        if (info1.getCustomerPhone().indexOf("+") != -1) {
            originalFile2 = infos.getRandomNumber() + info1.getCustomerName() + (info1.getCustomerPhone().substring(0, info1.getCustomerPhone().indexOf("+")));
            originalFile3 = info1.getCustomerName() + (info1.getCustomerPhone().substring(0, info1.getCustomerPhone().indexOf("+")));
        } else {
            originalFile2 = infos.getRandomNumber() + info1.getCustomerName() + info1.getCustomerPhone();
            originalFile3 = info1.getCustomerName() + info1.getCustomerPhone();
        }
        if ("术前".equals(iconType1)) {
            originalFileName = alldiagnosetime.substring(0, 10).replace("-", "") + "_" +
                    alldiagnosetime.split(" ")[1].split(":")[0] +
                    alldiagnosetime.split(" ")[1].split(":")[1] + alldiagnosetime.split(" ")[1].split(":")[2] + "-" +
                    info1.getCustomerName() + "-" + iconType1 + "-" + toothPosition + "-" + doctor + ".jpg";
            yoriginalFileName = info1.getCustomerName() + "-" + iconType1 + ".jpg";
            String httpUrl1 = "photo/" + abbreviation + "/" + originalFile1 + "/" + originalFile3 + "/" + yoriginalFileName;
            String httpUrl2 = "photoScoring/" + abbreviation + "/" + originalFile1 + "/" + originalFile2 + "/" + originalFileName;
            try {
                // 得到文件夹名
                OSSClient ossClient = new OSSClient(MemberEnumConfig.endPoint, MemberEnumConfig.accessKeyId, MemberEnumConfig.accessKeySecret);
                // 文件夹名OSSClient
                final String keySuffixWithSlash = httpUrl2;
                // 判断文件夹是否存在，不存在则创建
                if (!ossClient.doesObjectExist(MemberEnumConfig.backetName, keySuffixWithSlash)) {
                    // 创建文件夹
                    ossClient.putObject(MemberEnumConfig.backetName, keySuffixWithSlash, new ByteArrayInputStream(new byte[0]));
                }
                OSSObject object = ossClient.getObject(MemberEnumConfig.backetName, httpUrl1);
                String fileDir = object.getKey();
                OSSObject object1 = ossClient.getObject(MemberEnumConfig.backetName, httpUrl2);
                String fileDir1 = object1.getKey();
                CopyObjectResult result = ossClient.copyObject(MemberEnumConfig.backetName, fileDir, MemberEnumConfig.backetName, fileDir1);
                System.out.println("ETag: " + result.getETag() + " LastModified: " + result.getLastModified());
                // 关闭OSSClient。
                ossClient.shutdown();
                String sql = "insert into icon_infos (customer_id,outpatient_name,abbreviation,add_name," +
                        "add_time,image_url,icon_name,reservations_id,diagnoseTime,pay_service_id,customer_name,customer_phone,tooth_position,doctor,remarks,icon_name1,num) values ('" + customerId + "'," +
                        "'" + outpatientName + "','" + abbreviation + "','"
                        + userAccount + "','" + time + "','" + MemberEnumConfig.ossphoeurl + httpUrl2
                        + "','术前','" + reservationsId + "','" + alldiagnosetime + "','" + payServiceId + "','" + info1.getCustomerName() + "','" + info1.getCustomerPhone() + "','" + toothPosition + "'," +
                        "'" + doctor + "','" + remarks + "','3','" + infos.getRandomNumber() + "')";
                sqlMapper.insert(sql);
                String deleteSql = "delete from icon_infos1 where customer_id='" + customerId + "' and icon_name='术前'";
                sqlMapper.delete(deleteSql);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        if ("术中".equals(iconType2)) {
            originalFileName = alldiagnosetime.substring(0, 10).replace("-", "") + "_" +
                    alldiagnosetime.split(" ")[1].split(":")[0] +
                    alldiagnosetime.split(" ")[1].split(":")[1] + alldiagnosetime.split(" ")[1].split(":")[2] + "-" +
                    info1.getCustomerName() + "-" + iconType2 + "-" + toothPosition + "-" + doctor + ".jpg";
            yoriginalFileName = info1.getCustomerName() + "-" + iconType2 + ".jpg";
            //新 照片文件夹路径
            realNewPath = "D:\\8068\\apache-tomcat-8.0.30\\webapps\\ROOT\\photoScoring\\" + abbreviation + "\\" + originalFile1 + "\\" + originalFile2;
            httpUrl = "http://118.123.15.60:8068/photoScoring/" + abbreviation + "/" + originalFile1 + "/" + originalFile2 + "/";
            //数据库URL 路径
            imageurl = httpUrl + "\\" + originalFileName;
            imageurls = imageurl.replace("\\", "");
            String httpUrl1 = "photo/" + abbreviation + "/" + originalFile1 + "/" + originalFile3 + "/" + yoriginalFileName;
            String httpUrl2 = "photoScoring/" + abbreviation + "/" + originalFile1 + "/" + originalFile2 + "/" + originalFileName;
            try {
                OSSClient ossClient = new OSSClient(MemberEnumConfig.endPoint, MemberEnumConfig.accessKeyId, MemberEnumConfig.accessKeySecret);
                // 文件夹名OSSClient
                final String keySuffixWithSlash = httpUrl2;
                // 判断文件夹是否存在，不存在则创建
                if (!ossClient.doesObjectExist(MemberEnumConfig.backetName, keySuffixWithSlash)) {
                    // 创建文件夹
                    ossClient.putObject(MemberEnumConfig.backetName, keySuffixWithSlash, new ByteArrayInputStream(new byte[0]));
                }
                OSSObject object = ossClient.getObject(MemberEnumConfig.backetName, httpUrl1);
                String fileDir = object.getKey();
                OSSObject object1 = ossClient.getObject(MemberEnumConfig.backetName, httpUrl2);
                String fileDir1 = object1.getKey();
                CopyObjectResult result = ossClient.copyObject(MemberEnumConfig.backetName, fileDir, MemberEnumConfig.backetName, fileDir1);
                System.out.println("ETag: " + result.getETag() + " LastModified: " + result.getLastModified());
                // 关闭OSSClient。
                ossClient.shutdown();
                String sql = "insert into icon_infos (customer_id,outpatient_name,abbreviation,add_name," +
                        "add_time,image_url,icon_name,reservations_id,diagnoseTime,pay_service_id,customer_name,customer_phone,tooth_position,doctor,remarks,icon_name1,num) values ('" + customerId + "'," +
                        "'" + outpatientName + "','" + abbreviation + "','"
                        + userAccount + "','" + time + "','" + MemberEnumConfig.ossphoeurl + httpUrl2
                        + "','术中','" + reservationsId + "','" + alldiagnosetime + "','" + payServiceId + "','" + info1.getCustomerName() + "','" + info1.getCustomerPhone() + "','" + toothPosition + "','" + doctor + "'," +
                        "'" + remarks + "','2','" + infos.getRandomNumber() + "')";
                sqlMapper.insert(sql);
                String deleteSql = "delete from icon_infos1 where customer_id='" + customerId + "' and icon_name='术中'";
                sqlMapper.delete(deleteSql);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        if ("术后".equals(iconType3)) {
            originalFileName = alldiagnosetime.substring(0, 10).replace("-", "") + "_" +
                    alldiagnosetime.split(" ")[1].split(":")[0] +
                    alldiagnosetime.split(" ")[1].split(":")[1] + alldiagnosetime.split(" ")[1].split(":")[2] + "-" +
                    info1.getCustomerName() + "-" + iconType3 + "-" + toothPosition + "-" + doctor + ".jpg";
            yoriginalFileName = info1.getCustomerName() + "-" + iconType3 + ".jpg";
            //新 照片文件夹路径
            httpUrl = "http://118.123.15.60:8068/photoScoring/" + abbreviation + "/" + originalFile1 + "/" + originalFile2 + "/";
            imageurl = httpUrl + "\\" + originalFileName;
            imageurls = imageurl.replace("\\", "");
            String httpUrl1 = "photo/" + abbreviation + "/" + originalFile1 + "/" + originalFile3 + "/" + yoriginalFileName;
            String httpUrl2 = "photoScoring/" + abbreviation + "/" + originalFile1 + "/" + originalFile2 + "/" + originalFileName;
            try {
                OSSClient ossClient = new OSSClient(MemberEnumConfig.endPoint, MemberEnumConfig.accessKeyId, MemberEnumConfig.accessKeySecret);
                // 文件夹名OSSClient
                final String keySuffixWithSlash = httpUrl2;
                // 判断文件夹是否存在，不存在则创建
                if (!ossClient.doesObjectExist(MemberEnumConfig.backetName, keySuffixWithSlash)) {
                    // 创建文件夹
                    ossClient.putObject(MemberEnumConfig.backetName, keySuffixWithSlash, new ByteArrayInputStream(new byte[0]));
                }
                OSSObject object = ossClient.getObject(MemberEnumConfig.backetName, httpUrl1);
                String fileDir = object.getKey();
                OSSObject object1 = ossClient.getObject(MemberEnumConfig.backetName, httpUrl2);
                String fileDir1 = object1.getKey();
                CopyObjectResult result = ossClient.copyObject(MemberEnumConfig.backetName, fileDir, MemberEnumConfig.backetName, fileDir1);
                System.out.println("ETag: " + result.getETag() + " LastModified: " + result.getLastModified());
                // 关闭OSSClient。
                ossClient.shutdown();
                String sql = "insert into icon_infos (customer_id,outpatient_name,abbreviation,add_name," +
                        "add_time,image_url,icon_name,reservations_id,diagnoseTime,pay_service_id,customer_name,customer_phone,tooth_position,doctor,remarks,icon_name1,num) values ('" + customerId + "'," +
                        "'" + outpatientName + "','" + abbreviation + "','"
                        + userAccount + "','" + time + "','" + MemberEnumConfig.ossphoeurl + httpUrl2
                        + "','术后','" + reservationsId + "','" + alldiagnosetime + "','" + payServiceId + "','" + info1.getCustomerName() + "','" + info1.getCustomerPhone() + "','" + toothPosition + "','" + doctor + "'," +
                        "'" + remarks + "','1','" + infos.getRandomNumber() + "')";
                sqlMapper.insert(sql);
                String deleteSql = "delete from icon_infos1 where customer_id='" + customerId + "' and icon_name='术后'";
                sqlMapper.delete(deleteSql);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        } else {
            return "";
        }
        return MemberEnumConfig.ossphoeurl;
    }

    public List<ApprovalProcessInfo> getImg(String num) {
        String sql = "select * from icon_infos where num='" + num + "';";
        List<ApprovalProcessInfo> info2 = sqlMapper.selectList(sql, ApprovalProcessInfo.class);
        return info2;
    }
    public List<IconInfo> getIntelligentImgcancel1(String reservationsId, String num) {
        String sql = "select * from icon_infos1 where reservations_id='" + reservationsId + "' and num='" + num + "' order by icon_name1 DESC;";
        List<IconInfo> iconinfoList = sqlMapper.selectList(sql, IconInfo.class);
        return iconinfoList;
    }
    public List<IconInfo> getIntelligentImgcancel(String reservationsId, String num) {
        String sql = "select * from icon_infos where reservations_id='" + reservationsId + "' and num='" + num + "' order by icon_name1 DESC;";
        List<IconInfo> iconinfoList = sqlMapper.selectList(sql, IconInfo.class);
        return iconinfoList;
    }

    public int delteProcess(String id) throws Exception {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        //找到文件夹的url
        String selSql = "select id,image_url from icon_infos where id='" + id + "'";
        IconInfo info = sqlMapper.selectOne(selSql, IconInfo.class);
        OSSClient ossClient = new OSSClient(MemberEnumConfig.endPoint, MemberEnumConfig.accessKeyId, MemberEnumConfig.accessKeySecret);
        OSSObject object1 = ossClient.getObject(MemberEnumConfig.backetName, "photoScoring/" + info.getImageUrl().split("photoScoring/")[1]);
        String fileDir1 = object1.getKey();
        try {
            // 删除文件。如需删除文件夹，请将ObjectName设置为对应的文件夹名称。如果文件夹非空，则需要将文件夹下的所有object删除后才能删除该文件夹。
            ossClient.deleteObject(MemberEnumConfig.backetName, fileDir1);
            // 关闭OSSClient。
            ossClient.shutdown();
        } catch (Exception e) {
            throw new Exception("删除oss文件出错");
        } finally {
            ossClient.shutdown();
        }
        String sql = "delete from icon_infos where id='" + id + "'";
        return sqlMapper.delete(sql);
    }

    public int delteProcess2(String id) throws Exception {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        //找到文件夹的url
        String selSql = "select id,image_url from icon_infos1 where id='" + id + "'";
        IconInfo info = sqlMapper.selectOne(selSql, IconInfo.class);
        //        String httpUrl = info.getImageUrl();
//        String urlName = httpUrl.substring(httpUrl.indexOf("photo") + 6).replace("/", "\\");
//        String pathUrl = "D:\\8068\\apache-tomcat-8.0.30\\webapps\\ROOT\\photo\\" + urlName + "";
//        File file = new File(pathUrl); //文件所在的路径
//        deleteDirectory(file);
        OSSClient ossClient = new OSSClient(MemberEnumConfig.endPoint, MemberEnumConfig.accessKeyId, MemberEnumConfig.accessKeySecret);
        OSSObject object1 = ossClient.getObject(MemberEnumConfig.backetName, "photo/" + info.getImageUrl().split("photo/")[1]);
        String fileDir1 = object1.getKey();
        try {
            // 删除文件。如需删除文件夹，请将ObjectName设置为对应的文件夹名称。如果文件夹非空，则需要将文件夹下的所有object删除后才能删除该文件夹。
            ossClient.deleteObject(MemberEnumConfig.backetName, fileDir1);
            // 关闭OSSClient。
            ossClient.shutdown();
        } catch (Exception e) {
            throw new Exception("删除oss文件出错");
        } finally {
            ossClient.shutdown();
        }
        String sql = "delete from icon_infos1 where id='" + id + "'";
        return sqlMapper.delete(sql);
    }

    public int delteProcess4(String customerId) throws Exception {
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        //找到文件夹的url
        String selSql = "select id,image_url from icon_infos where customer_id='" + customerId + "'";
        List<IconInfo> infoList = sqlMapper.selectList(selSql, IconInfo.class);
        String httpUrl = infoList.get(0).getImageUrl();
//        String urlName = httpUrl.substring(httpUrl.indexOf("photoScoring") + 13).replace("/", "\\");
//        String pathName = urlName.substring(0, urlName.lastIndexOf("\\"));
//        String pathUrl = "D:\\8068\\apache-tomcat-8.0.30\\webapps\\ROOT\\photoScoring\\" + pathName + "";
//        File file = new File(pathUrl);//文件所在的路径
//        deleteDirectory(file);
        OSSClient ossClient = new OSSClient(MemberEnumConfig.endPoint, MemberEnumConfig.accessKeyId, MemberEnumConfig.accessKeySecret);
        try {
            for (IconInfo iconInfo : infoList) {
                OSSObject object1 = ossClient.getObject(MemberEnumConfig.backetName, "photoScoring/" + iconInfo.getImageUrl().split("photoScoring/")[1]);
                String fileDir1 = object1.getKey();
                // 删除文件。如需删除文件夹，请将ObjectName设置为对应的文件夹名称。如果文件夹非空，则需要将文件夹下的所有object删除后才能删除该文件夹。
                ossClient.deleteObject(MemberEnumConfig.backetName, fileDir1);
            }
            // 关闭OSSClient。
            ossClient.shutdown();
        } catch (Exception e) {
            throw new Exception("删除oss文件出错");
        } finally {
            ossClient.shutdown();
        }
        String sql = "delete from icon_infos1 where customer_id='" + customerId + "'";
        return sqlMapper.delete(sql);
    }

    public int deleteScoringIcons(String num) throws Exception {
        String selSql = "select id,image_url from icon_infos where num='" + num + "';";
        List<IconInfo> iconInfoList = sqlMapper.selectList(selSql, IconInfo.class);
//        String httpUrl = iconInfo.getImageUrl();
//        String urlName = httpUrl.substring(httpUrl.indexOf("photoScoring") + 13).replace("/", "\\");
//        String pathName = urlName.substring(0, urlName.lastIndexOf("\\"));
//        String pathUrl = "D:\\8068\\apache-tomcat-8.0.30\\webapps\\ROOT\\photoScoring\\" + pathName + "";
//        File file = new File(pathUrl);//文件所在的路径
//        deleteDirectory(file);
        OSSClient ossClient = new OSSClient(MemberEnumConfig.endPoint, MemberEnumConfig.accessKeyId, MemberEnumConfig.accessKeySecret);
        try {
            for (IconInfo iconInfo : iconInfoList) {
                OSSObject object1 = ossClient.getObject(MemberEnumConfig.backetName, "photoScoring/" + iconInfo.getImageUrl().split("photoScoring/")[1]);
                String fileDir1 = object1.getKey();
                // 删除文件。如需删除文件夹，请将ObjectName设置为对应的文件夹名称。如果文件夹非空，则需要将文件夹下的所有object删除后才能删除该文件夹。
                ossClient.deleteObject(MemberEnumConfig.backetName, fileDir1);
            }
            // 关闭OSSClient。
            ossClient.shutdown();
        } catch (Exception e) {
            throw new Exception("删除oss文件出错");
        } finally {
            ossClient.shutdown();
        }
        String sql = "delete from icon_infos where num='" + num + "'";
        return sqlMapper.delete(sql);
    }

    public List<DoctorSchedulingInfo> findDoctorSchedulingList(String startDate, String endDate, String outpatientName) {
        String getListSql = " select a.id as id,a.account as doctor from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on find_in_set(c.id,a.roleid) > 0 " +
                " where (c.name='医生' or c.name='洁牙师') and a.status='1' and b.simplename= '" + outpatientName + "'";
        List<ConsultantInfo> docList = sqlMapper.selectList(getListSql, ConsultantInfo.class); //根据角色用户查询所有医生
        String sql1 = "select id as id,doctor_id as doctorId,status as status ,remarks as remarks,create_time as createTime,update_people as updatePeople" +
                ",update_time as updateTime,the_date as theDate from doctor_scheduling where the_date >='" + startDate + "'and the_date <='" + endDate + "' and outpatient_name ='" + outpatientName + "' ";
        List<DoctorSchedulingInfo> schList = sqlMapper.selectList(sql1, DoctorSchedulingInfo.class); //根据当前日期查询 所有的排班信息
        //循环医生集合,组装每个医生周一至周日的排班情况,默认是正常班,通过查询医生排班表已设置的休息记录给对应医生对应的日期赋值对应的排班状态
        List<DoctorSchedulingInfo> tableList = new ArrayList<DoctorSchedulingInfo>();
        for (ConsultantInfo doc : docList) {//实例化一个医生对应的一周排班实体对象
            DoctorSchedulingInfo docSch = new DoctorSchedulingInfo();
            docSch.setDoctorId(doc.getId());
            docSch.setDoctorName(doc.getDoctor());
            List<String> dateList = DateUtil.getDays(startDate, endDate); //获取起始日期的每天的集合,比方说起点是1号,那么我们就要把1号到7号所有日期都列出来,然后循环排班班是否在对应的日期有休息的排班
            for (int i = 0; i < 7; i++) {
                String curDate = dateList.get(i);
                boolean isSch = false;//默认正常班
                for (DoctorSchedulingInfo sch : schList) { //循环医生排班集合,判断对应日期是否有排班
                    if (sch.getDoctorId().equals(doc.getId()) && sch.getTheDate().equals(curDate) && sch.getStatus().toString().equals("2")) {
                        isSch = true;//如果当前医生对应日期有排班,且状态为2 休息 就退出当前排班循环
                        break;
                    }
                }
                if (isSch) {//如果是排班休息
                    if (i == 0) {///周一
                        docSch.setMondayStatus("2");
                    } else if (i == 1) {///周二
                        docSch.setTuesdayStatus("2");
                    } else if (i == 2) {///周三
                        docSch.setWednesdayStatus("2");
                    } else if (i == 3) {///周四
                        docSch.setThursdayStatus("2");
                    } else if (i == 4) {///周五
                        docSch.setFridayStatus("2");
                    } else if (i == 5) {///周六
                        docSch.setSaturdayStatus("2");
                    } else if (i == 6) {///周日
                        docSch.setSundayStatus("2");
                    }
                }
            }
            tableList.add(docSch); //加入到返回前端集合中
        }
        return tableList;
    }

    public int updateDoctorStatus(String doctorName, String doctorId, String docTime, String docStatus, String outpatientName,String userAccount) {
        String[] times = docTime.split(",");//适配一个或者多个时间,如果只穿了1个times数组就是一个
        for (String curTime : times) {
            String sql2 = "delete from doctor_scheduling where doctor_id = '" + doctorId + "' and the_date = '" + curTime + "' and outpatient_name ='" + outpatientName + "'";
            sqlMapper.delete(sql2);
            String sql = "insert into doctor_scheduling (doctor_id,doctor_name,the_date,status,update_time,update_people,outpatient_name) VALUES ('" + doctorId + "','" + doctorName + "','" + curTime + "','" + docStatus + "','" + DateTimeKit.now() + "','" + userAccount + "','" + outpatientName + "')";
            sqlMapper.insert(sql);
        }
        return 1;
    }

    public List<ConsultantInfo> getDataListByDoctor(String timeSlot, String abbreviation) {
        String startTime = timeSlot.split(",")[0].substring(0, 11);
        String doctor = timeSlot.split(",")[2];
        //根据角色用户查询所有医生
        String sql = " select t1.id as id,t.customer_name as customerName,t.customer_age as customerAge,customer_status as customerStatus,t.customer_phone as customerPhone,t.medical_record_number as medicalRecordNumber," +
                " ( " +
                " CASE t.customer_sex " +
                " WHEN 1 THEN " +
                "     '男' " +
                " WHEN 2 THEN " +
                "     '女' " +
                " ELSE " +
                "     '未知' " +
                " END " +
                "    ) as sex,t.archiving as archiving ,t.reservations_id as reservationsId,t.arrival_status as arrivalStatus ," +
                " t.arrival_store as arrivalStore ,t.birthday as birthday ,left(t.create_appointment_time,19) as createAppointmentTime," +
                " t.field_tc as fieldTc,t.first_visit_date as firstVisitDate,t.appointment_pay_service as appointmentPayService ," +
                " t.start_time as startTime, t.end_time as endTime,t.doctor,t.id as customerId,t.account_arrears as accountarrears " +
                " from " + abbreviation+"customer_info t " +
                " left join user t1 on t.doctor=t1.account where left(t.start_time,10) = '" + startTime.substring(0, 10) + "' " +
                " and doctor='" + doctor + "' and t.arrival_status!=8 ";
        List<ConsultantInfo> DataList = sqlMapper.selectList(sql, ConsultantInfo.class);
        return DataList;
    }

    public String getDoctorStatus(String doctor, String value, String outpatientName) {
        String sql = "select t.status from doctor_scheduling t where t.doctor_name='" + doctor + "' and left(t.the_date,10)='" + value.substring(0, 10) + "' and t.outpatient_name='" + outpatientName + "' limit 1";
        DoctorLookedInfo doctorLookedInfo = sqlMapper.selectOne(sql, DoctorLookedInfo.class);
        if (doctorLookedInfo == null) {
            return "1";
        } else {
            return doctorLookedInfo.getStatus();
        }
    }

    public List<DoctorSchedulingInfo> getRestingState(String timeSlot, String outpatientName) {
        String startTime = timeSlot.split(",")[0];
        String endTime = timeSlot.split(",")[1];
        String doctorId = timeSlot.split(",")[2];
        String sql1 = " select t.doctor_id as doctorId ,t1.account as doctorName ,t.the_date as theDate" +
                " from doctor_scheduling t  left join user t1 on t.doctor_id=t1.id " +
                " where t.doctor_id='" + doctorId + "' and t.status=2 " +
                " and t.the_date between '" + startTime + "' and '" + endTime + "' and t.outpatient_name='" + outpatientName + "' ";
        List<DoctorSchedulingInfo> schedulingInfo = sqlMapper.selectList(sql1, DoctorSchedulingInfo.class);
        return schedulingInfo;
    }

    public List<ConsultantInfo> getDataDoctor(String timeSlot, String abbreviation) {
        String startTime = timeSlot.split(",")[0].substring(0, 11);
        String endTime = timeSlot.split(",")[1].substring(0, 11);
        String doctorId = timeSlot.split(",")[2];
        String startTime2 = timeSlot.split(",")[0];
        String endTime2 = timeSlot.split(",")[1];
        String sql1 = "";
        if (startTime.equals(endTime)) {
            sql1 += "t.start_time  like CONCAT('%" + startTime + "%')";
        } else {
            sql1 += "t.start_time >=  '" + startTime2 + "' and t.end_time <= '" + endTime2 + "'";
        }
        String sql2 = "select id,account from user where id ='" + doctorId + "' limit 1";
        User user = sqlMapper.selectOne(sql2, User.class);
        String sql = " select * from (select t1.id as id,t.customer_name as customerName,customer_status as customerStatus,t.customer_age as customerAge,t.customer_phone as customerPhone,t.medical_record_number as medicalRecordNumber," +
                "     ( " +
                " CASE t.customer_sex " +
                " WHEN 1 THEN " +
                "     '男' " +
                " WHEN 2 THEN " +
                "     '女' " +
                " ELSE " +
                "     '未知' " +
                " END " +
                "    ) as sex ,t.archiving as archiving ,t.reservations_id as reservationsId,t.yellow_number as yellowNumber," +
                "t.treatment_unit as treatmentUnit,t.account_arrears as accountArrears," +
                "(select count(id) from " + abbreviation+"customer_info b where t.reservations_id=b.reservations_id and left(start_time,10)=left(now(),10)) as todaySum," +
                "(select count(id) from " + abbreviation+"customer_info b where t.reservations_id=b.reservations_id and left(start_time,10)>left(now(),10)) as aftersSum," +
                "(select count(id) from " + abbreviation+"customer_info b where t.reservations_id=b.reservations_id and left(start_time,10)<left(now(),10)) as beforeSum," +
                "t.arrival_status as arrivalStatus,t.arrival_store as arrivalStore ,t.birthday as birthday ," +
                "left(t.create_appointment_time,19) as createAppointmentTime,t.remarks as remarks,t.remarks_reservation as remarksReservation, t.field_tc as fieldTc,t.first_visit_date as firstVisitDate," +
                "t.appointment_pay_service as appointmentPayService ,t.start_time as startTime, t.end_time as endTime,t.doctor,t.id as customerId " +
                " from " + abbreviation+"customer_info t " +
                " left join user t1 on t.doctor=t1.account where " + sql1 + " " +
                " and t.arrival_status!=8 and doctor='" + user.getAccount() + "' order by t.create_appointment_time) t2 ";
        List<ConsultantInfo> DataWeekList = sqlMapper.selectList(sql, ConsultantInfo.class);
        return DataWeekList;
    }

    public List<ConsultantInfo> getOneDoctor(String timeSlot, String abbreviation) {
        String startTime = timeSlot.split(",")[0].substring(0, 11);
        String endTime = timeSlot.split(",")[1].substring(0, 11);
        String doctorId = timeSlot.split(",")[2];
        String sql1 = "";
        if (startTime.equals(endTime)) {
            sql1 += "t.start_time  like CONCAT('%" + startTime + "%')";
        }
        String sql2 = "select id,account from user where id ='" + doctorId + "' limit 1";
        User user = sqlMapper.selectOne(sql2, User.class);
        String sql = " select t1.id as id,t.customer_name as customerName,t.customer_age as customerAge,t.customer_phone as customerPhone,t.medical_record_number as medicalRecordNumber," +
                " ( " +
                " CASE t.customer_sex " +
                " WHEN 1 THEN " +
                "     '男' " +
                " WHEN 2 THEN " +
                "     '女' " +
                " ELSE " +
                "     '未知' " +
                " END " +
                "    ) as sex,t.archiving as archiving ,t.arrival_status as arrivalStatus ,t.arrival_store as arrivalStore ,t.birthday as birthday ," +
                " left(t.create_appointment_time,19) as createAppointmentTime,t.field_tc as fieldTc,t.first_visit_date as firstVisitDate," +
                " t.appointment_pay_service as appointmentPayService ,t.start_time as startTime, t.end_time as endTime,t.doctor,t.id as customerId from " + abbreviation+"customer_info t " +
                " left join user t1 on t.doctor=t1.account where " + sql1 + " and doctor='" + user.getAccount() + "'";
        List<ConsultantInfo> DataOneList = sqlMapper.selectList(sql, ConsultantInfo.class);
        return DataOneList;
    }

    public String upLoadOrthodontics1(MultipartFile picture, String cri, String userAccount) {
        String num = cri.split(",")[0];
        String imgtpye = cri.split(",")[1];
        String classification = cri.split(",")[2];
        String reservationsId = cri.split(",")[3];
        String realPath = "D:\\8068\\apache-tomcat-8.0.30\\webapps\\ROOT\\yingxiang\\orthodontics";
        String httpUrl = "http://118.123.15.60:8068/yingxiang/orthodontics/";
        //避免出现相同的图片名称  ids
        String randomNumbersql = "select DATE_FORMAT(now(),'%Y%m%d%k%i%S') as randomNumber from dual";
        DaulInfo infos = sqlMapper.selectOne(randomNumbersql, DaulInfo.class);
        String imageurl = "yingxiang/orthodontics/" + infos.getRandomNumber() + ".png";
        try {
//            FileUtils.copyInputStreamToFile(picture.getInputStream(), new File(realPath, infos.getRandomNumber() + ".png"));
            aliyunOssUtil.pushSingleFile(picture, imageurl);
            String sql = "INSERT INTO `root_orthodontics_imgs_info` (`image_url`, `num`, `imgtpye`, `classification`,reservations_id,create_name,status) VALUES " +
                    "( '" + MemberEnumConfig.ossphoeurl + imageurl + "', '" + num + "', '" + imgtpye + "', '" + classification + "','" + reservationsId + "','" + userAccount + "','1');";
            sqlMapper.insert(sql);
            String upSql = "update root_orthodontics_info a set a.shu_status=1,a.create_time='" + DateTimeKit.now() + "' where a.num='" + num + "' and a.reservations_id='" + reservationsId + "'";
            sqlMapper.insert(upSql);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return MemberEnumConfig.ossphoeurl + imageurl;
    }

    public String upLoadOrthodontics(MultipartFile picture, String cri, String userAccount) {
        String num = cri.split(",")[0];
        String imgtpye = cri.split(",")[1];
        String classification = cri.split(",")[2];
        String reservationsId = cri.split(",")[3];
        String realPath = "D:\\8068\\apache-tomcat-8.0.30\\webapps\\ROOT\\yingxiang\\orthodontics";
        //String httpUrl = "http://118.123.15.60:8068/yingxiang/orthodontics/";
        //避免出现相同的图片名称  ids
        String randomNumbersql = "select DATE_FORMAT(now(),'%Y%m%d%k%i%S') as randomNumber from dual";
        DaulInfo infos = sqlMapper.selectOne(randomNumbersql, DaulInfo.class);
        String imageurl = "yingxiang/orthodontics/" + infos.getRandomNumber()+num.substring(num.length()-6)+".png";
        try {
//            FileUtils.copyInputStreamToFile(picture.getInputStream(), new File(realPath, infos.getRandomNumber() +".png"));
            aliyunOssUtil.pushSingleFile(picture, imageurl);
            String sql = "INSERT INTO `root_orthodontics_imgs_info` (`image_url`, `num`, `imgtpye`, `classification`,reservations_id,create_name) VALUES " +
                    "( '" + MemberEnumConfig.ossphoeurl+imageurl + "', '" + num + "', '" + imgtpye + "', '" + classification + "','" + reservationsId + "','"+userAccount+"');";
            sqlMapper.insert(sql);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return MemberEnumConfig.ossphoeurl+imageurl;
    }

    public String upLoadRootCanal(MultipartFile picture, String cri, String userAccount) {
        String num = cri.split(",")[0];
        String reservationsId = cri.split(",")[1];
        String realPath = "D:\\8068\\apache-tomcat-8.0.30\\webapps\\ROOT\\yingxiang\\rootCanal";
        String httpUrl = "http://118.123.15.60:8068/yingxiang/rootCanal/";
        //避免出现相同的图片名称  ids
        String randomNumbersql = "select DATE_FORMAT(now(),'%Y%m%d%k%i%S') as randomNumber from dual";
        DaulInfo infos = sqlMapper.selectOne(randomNumbersql, DaulInfo.class);
        String imageurl = "yingxiang/rootCanal/" + infos.getRandomNumber()+".png";
        try {
            aliyunOssUtil.pushSingleFile(picture, imageurl);
            //FileUtils.copyInputStreamToFile(picture.getInputStream(), new File(realPath,infos.getRandomNumber() +".png"));
            String sql = "insert into root_canal_imgs_info (num, image_url,reservations_id,create_name) VALUES " +
                    "('" + num + "','" +MemberEnumConfig.ossphoeurl+imageurl + "','" + reservationsId + "','"+userAccount+"');";
            sqlMapper.insert(sql); //上传主图
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return MemberEnumConfig.ossphoeurl+imageurl;
    }

    public String uploadScaling(MultipartFile picture, String cri, String userAccount) {
        String num = cri.split(",")[0];
        String imgtpye = cri.split(",")[1];
        String classification = cri.split(",")[2];
        String reservationsId = cri.split(",")[3];
        String realPath = "D:\\8068\\apache-tomcat-8.0.30\\webapps\\ROOT\\yingxiang\\scaling";
        String httpUrl = "http://118.123.15.60:8068/yingxiang/scaling/";
        //避免出现相同的图片名称  ids
        String randomNumbersql = "select DATE_FORMAT(now(),'%Y%m%d%k%i%S') as randomNumber from dual";
        DaulInfo infos = sqlMapper.selectOne(randomNumbersql, DaulInfo.class);
        String imageurl = "yingxiang/scaling/" + infos.getRandomNumber()+".png";
        try {
//            FileUtils.copyInputStreamToFile(picture.getInputStream(), new File(realPath, infos.getRandomNumber() +".png"));
            aliyunOssUtil.pushSingleFile(picture, imageurl);
            String sql = "INSERT INTO `root_scaling_imgs_info` (`image_url`, `num`, `imgtpye`, `classification`,reservations_id,create_name) VALUES " +
                    "( '"+MemberEnumConfig.ossphoeurl+imageurl + "', '" + num + "', '" + imgtpye + "', '" + classification + "','" + reservationsId + "','"+userAccount+"');";
            sqlMapper.insert(sql);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return MemberEnumConfig.ossphoeurl+imageurl;
    }

    public String uploadPeriodontal(MultipartFile picture, String cri, String userAccount) {
        String num = cri.split(",")[0];
        String imgtpye = cri.split(",")[1];
        String classification = cri.split(",")[2];
        String reservationsId = cri.split(",")[3];
        String realPath = "D:\\8068\\apache-tomcat-8.0.30\\webapps\\ROOT\\yingxiang\\periodontal";
        String httpUrl = "http://118.123.15.60:8068/yingxiang/periodontal/";
        //避免出现相同的图片名称  ids
        String randomNumbersql = "select DATE_FORMAT(now(),'%Y%m%d%k%i%S') as randomNumber from dual";
        DaulInfo infos = sqlMapper.selectOne(randomNumbersql, DaulInfo.class);
        String imageurl ="yingxiang/periodontal/" + infos.getRandomNumber()+".png";
        try {
//            FileUtils.copyInputStreamToFile(picture.getInputStream(), new File(realPath,infos.getRandomNumber() +".png"));
            aliyunOssUtil.pushSingleFile(picture, imageurl);
            String sql = "INSERT INTO `root_periodontal_imgs_info` (`image_url`, `num`, `imgtpye`, `classification`,reservations_id,create_name) VALUES " +
                    "( '"+MemberEnumConfig.ossphoeurl+ imageurl + "', '" + num + "', '" + imgtpye + "', '" + classification + "','" + reservationsId + "','"+userAccount+"');";
            sqlMapper.insert(sql);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return MemberEnumConfig.ossphoeurl+imageurl;
    }

    public String uploadPlantScoreFirst(MultipartFile picture, String cri, String userAccount) {
        String num = cri.split(",")[0];
        String imgtpye = cri.split(",")[1];
        String classification = cri.split(",")[2];
        String reservationsId = cri.split(",")[3];
        String realPath = "D:\\8068\\apache-tomcat-8.0.30\\webapps\\ROOT\\yingxiang\\periodontal";
        String httpUrl = "http://118.123.15.60:8068/yingxiang/periodontal/";
        //避免出现相同的图片名称  ids
        String randomNumbersql = "select DATE_FORMAT(now(),'%Y%m%d%k%i%S') as randomNumber from dual";
        DaulInfo infos = sqlMapper.selectOne(randomNumbersql, DaulInfo.class);
        String imageurl = "yingxiang/periodontal/" + infos.getRandomNumber()+".png";
        try {
//            FileUtils.copyInputStreamToFile(picture.getInputStream(), new File(realPath, infos.getRandomNumber() +".png"));
            aliyunOssUtil.pushSingleFile(picture, imageurl);
            String sql = "INSERT INTO `root_plant_imgs_info` (`image_url`, `num`, `imgtpye`, `classification`,reservations_id,create_name) VALUES " +
                    "( '" +MemberEnumConfig.ossphoeurl+ imageurl + "', '" + num + "', '" + imgtpye + "', '" + classification + "','" + reservationsId + "','"+userAccount+"');";
            sqlMapper.insert(sql);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return MemberEnumConfig.ossphoeurl+imageurl;
    }

    public String uploadRepair(MultipartFile picture, String cri, String userAccount) {
        String num = cri.split(",")[0];
        String imgtpye = cri.split(",")[1];
        String classification = cri.split(",")[2];
        String reservationsId = cri.split(",")[3];
        String realPath = "D:\\8068\\apache-tomcat-8.0.30\\webapps\\ROOT\\yingxiang\\periodontal";
        String httpUrl = "http://118.123.15.60:8068/yingxiang/periodontal/";
        //避免出现相同的图片名称  ids
        String randomNumbersql = "select DATE_FORMAT(now(),'%Y%m%d%k%i%S') as randomNumber from dual";
        DaulInfo infos = sqlMapper.selectOne(randomNumbersql, DaulInfo.class);
        String imageurl ="yingxiang/periodontal/" + infos.getRandomNumber()+".png";
        try {
//            FileUtils.copyInputStreamToFile(picture.getInputStream(), new File(realPath, infos.getRandomNumber() +".png"));
            aliyunOssUtil.pushSingleFile(picture, imageurl);
            String sql = "INSERT INTO `root_repair_imgs_info` (`image_url`, `num`, `imgtpye`, `classification`,reservations_id,create_name) VALUES " +
                    "( '"+MemberEnumConfig.ossphoeurl+ imageurl + "', '" + num + "', '" + imgtpye + "', '" + classification + "','" + reservationsId + "','"+userAccount+"');";
            sqlMapper.insert(sql);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return MemberEnumConfig.ossphoeurl+imageurl;
    }

    public int storageOrthodontics(String reservationsId, String num, String doctor, String nurse, String customerName, String sex, String orthodonticsAge,
                                   String orthodonticDesign, String congestionDegree, String coveringCoverage, String molarRelationship, String canineRelationship, String designScheme, String userAccount, String outpatientName) {
        String selsql = "select id from root_orthodontics_info where num='" + num + "' limit 1;";
        DaulInfo daulInfo = sqlMapper.selectOne(selsql, DaulInfo.class);
        if (daulInfo == null) {
            String insertSql = "INSERT INTO root_orthodontics_info (`reservations_id`,`num`,`create_name`,create_time,doctor,nurse,customer_name,customer_sex," +
                    "congestion_degree,covering_coverage,molar_relationship,canine_relationship,design_scheme,orthodontics_age,arrival_store,orthodontic_design) VALUES " +
                    "('" + reservationsId + "','" + num + "','" + userAccount + "','" + DateTimeKit.now() + "','" + doctor + "','" + nurse + "','" + customerName + "','" + sex + "'" +
                    ",'" + congestionDegree + "','" + coveringCoverage + "','" + molarRelationship + "','" + canineRelationship + "','" + designScheme + "','" + orthodonticsAge + "','" + outpatientName + "','" + orthodonticDesign + "');";
            sqlMapper.insert(insertSql);
        } else {
            String updateSql = "update root_orthodontics_info a set a.congestion_degree='" + congestionDegree + "',a.orthodontic_design='" + orthodonticDesign + "',a.doctor='" + doctor + "',a.nurse='" + nurse + "',a.covering_coverage='" + coveringCoverage + "',a.molar_relationship='" + molarRelationship + "',a.canine_relationship='" + canineRelationship + "'," +
                    "a.design_scheme='" + designScheme + "',a.orthodontics_age='" + orthodonticsAge + "',a.customer_sex='" + sex + "',a.create_time='" + DateTimeKit.now() + "' where num='" + num + "' and reservations_id='" + reservationsId + "' and create_name='" + userAccount + "'";
            sqlMapper.update(updateSql);
        }
        String upimgs = "update root_orthodontics_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                " and create_name='" + userAccount + "';";
        return sqlMapper.update(upimgs);
    }

    public int orthodonticScoring(String reservationsId, String num, String a1, String a2, String a3, String a4, String a5, String a6, String a7, String C1, String C2, String C3, String C4, String C5, String C6, String degreeRadio, String expertComment1, String expertComment2, String userAccount) {
        if (StringUtil.isEmpty(a1)) {
            a1 = "";
        }
        if (StringUtil.isEmpty(a2)) {
            a2 = "";
        }
        if (StringUtil.isEmpty(a3)) {
            a3 = "";
        }
        if (StringUtil.isEmpty(a4)) {
            a4 = "";
        }
        if (StringUtil.isEmpty(a5)) {
            a5 = "";
        }
        if (StringUtil.isEmpty(a6)) {
            a6 = "";
        }
        if (StringUtil.isEmpty(a7)) {
            a7 = "";
        }
        if (StringUtil.isEmpty(expertComment1)) {
            expertComment1 = "";
        }
        if (StringUtil.isEmpty(expertComment2)) {
            expertComment2 = "";
        }
        String updateSql = "update root_orthodontics_info a set a.A1='" + a1 + "',a.A2='" + a2 + "',a.A3='" + a3 + "',a.A4='" + a4 + "',a.A5='" + a5 + "',a.A6='" + a6 + "'," +
                "a.A7='" + a7 + "',a.hee_status = 1,a.C1='" + C1 + "',a.C2='" + C2 + "',a.C3='" + C3 + "',a.C4='" + C4 + "',a.C5='" + C5 + "',a.C6='" + C6 + "',a.degree_radio='" + degreeRadio + "',a.raters='" + userAccount + "',a.expert_comment1='" + expertComment1 + "',a.expert_comment2='" + expertComment2 + "' where num='" + num + "' and reservations_id='" + reservationsId + "'";
        return sqlMapper.update(updateSql);
    }

    public int subOrthodontics(String reservationsId, String num, String doctor, String nurse, String customerName, String sex, String orthodonticsAge, String orthodonticDesign,
                               String congestionDegree, String coveringCoverage, String molarRelationship, String canineRelationship, String designScheme,
                               String medicalRecordNumberValue, String userAccount, String outpatientName) {
        String selsql = "select id,doctor,nurse from root_orthodontics_info where num='" + num + "' limit 1;";
        DaulInfo daulInfo = sqlMapper.selectOne(selsql, DaulInfo.class);
        if (daulInfo == null) {
            String insertSql = "INSERT INTO root_orthodontics_info (`reservations_id`,`num`,`create_name`,create_time,`status`,arrival_store,doctor,nurse,customer_name,congestion_degree,covering_coverage,molar_relationship,canine_relationship,design_scheme,orthodontics_age,customer_sex,medical_record_number,orthodontic_design,submission_state) VALUES " +
                    "('" + reservationsId + "','" + num + "','" +userAccount + "','" + DateTimeKit.now() + "',1,'" + outpatientName + "','" + doctor + "','" + nurse + "','" + customerName + "'," +
                    "'" + congestionDegree + "','" + coveringCoverage + "','" + molarRelationship + "','" + canineRelationship + "','" + designScheme + "','" + orthodonticsAge + "','" + sex + "','" + medicalRecordNumberValue + "','" + orthodonticDesign + "','1');";
            String upimgs = "update root_orthodontics_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            sqlMapper.update(upimgs);
            return sqlMapper.insert(insertSql);
        } else {
            String updateSql = "update root_orthodontics_info a set a.status=1,a.submission_state=1,a.congestion_degree='" + congestionDegree + "',a.orthodontic_design='" + orthodonticDesign + "',a.doctor='" + doctor + "',a.nurse='" + nurse + "',a.covering_coverage='" + coveringCoverage + "',a.molar_relationship='" + molarRelationship + "'," +
                    "a.canine_relationship='" + canineRelationship + "', a.design_scheme='" + designScheme + "',a.orthodontics_age='" + orthodonticsAge + "',a.customer_sex='" + sex + "',a.create_time='" + DateTimeKit.now() + "',a.medical_record_number='" + medicalRecordNumberValue + "' where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            String upimgs = "update root_orthodontics_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            sqlMapper.update(upimgs);
            return sqlMapper.update(updateSql);
        }
    }

    public int subOrthodontics2(String reservationsId, String num, String doctor, String nurse, String customerName, String sex, String orthodonticsAge, String orthodonticDesign, String congestionDegree, String coveringCoverage,
                                String molarRelationship, String canineRelationship, String designScheme, String medicalRecordNumberValue, String userAccount, String outpatientName) {
        String selsql = "select id,doctor,nurse from root_orthodontics_info where num='" + num + "' limit 1;";
        DaulInfo daulInfo = sqlMapper.selectOne(selsql, DaulInfo.class);
        if (daulInfo == null) {
            String insertSql = "INSERT INTO root_orthodontics_info (`reservations_id`,`num`,`create_name`,create_time,`status`,arrival_store,doctor,nurse,customer_name,congestion_degree,covering_coverage,molar_relationship,canine_relationship,design_scheme,orthodontics_age,customer_sex,medical_record_number,orthodontic_design) VALUES " +
                    "('" + reservationsId + "','" + num + "','" + userAccount + "','" + DateTimeKit.now() + "',0,'" + outpatientName + "','" + doctor + "','" + nurse + "','" + customerName + "'," +
                    "'" + congestionDegree + "','" + coveringCoverage + "','" + molarRelationship + "','" + canineRelationship + "','" + designScheme + "','" + orthodonticsAge + "','" + sex + "','" + medicalRecordNumberValue + "','" + orthodonticDesign + "');";
            String upimgs = "update root_orthodontics_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            sqlMapper.update(upimgs);
            return sqlMapper.insert(insertSql);
        } else {
            String updateSql = "update root_orthodontics_info a set a.congestion_degree='" + congestionDegree + "',a.orthodontic_design='" + orthodonticDesign + "',a.doctor='" + doctor + "',a.nurse='" + nurse + "',a.covering_coverage='" + coveringCoverage + "',a.molar_relationship='" + molarRelationship + "'," +
                    "a.canine_relationship='" + canineRelationship + "', a.design_scheme='" + designScheme + "',a.orthodontics_age='" + orthodonticsAge + "',a.customer_sex='" + sex + "',a.create_time='" + DateTimeKit.now() + "',a.medical_record_number='" + medicalRecordNumberValue + "' where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            String upimgs = "update root_orthodontics_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            sqlMapper.update(upimgs);
            return sqlMapper.update(updateSql);
        }
    }

    public int storageOrthodonticsd(String reservationsId, String num, String doctor, String nurse, String molarTooth, String canineTooth, String overlap, String userAccount) {
        String updateSql = "update root_orthodontics_info a set a.doctor1='" + doctor + "',a.status=2,a.molar_tooth='" + molarTooth + "',a.canine_tooth='" + canineTooth + "',a.overlap='" + overlap + "',a.nurse1='" + nurse + "' " +
                " where num='" + num + "' and reservations_id='" + reservationsId + "' and create_name='" + userAccount + "'";
        String upimgs = "update root_orthodontics_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                " and create_name='" + userAccount + "';";
        sqlMapper.update(upimgs);
        return sqlMapper.update(updateSql);
    }
    public int subCore(String reservationsId, String num, String a1, String a2, String a3, String a4, String a5, String a6, String a7, String C1, String C2, String C3, String C4, String C5, String C6, String degreeRadio, String expertComment1, String expertComment2, String userAccount) {
        String updateSql = "update root_orthodontics_info a set a.A1='" + a1 + "',a.A2='" + a2 + "',a.A3='" + a3 + "',a.A4='" + a4 + "',a.A5='" + a5 + "',a.A6='" + a6 + "'," +
                "a.A7='" + a7 + "',a.C1='" + C1 + "',a.C2='" + C2 + "',a.C3='" + C3 + "',a.C4='" + C4 + "',a.C5='" + C5 + "',a.C6='" + C6 + "',a.degree_radio = '" + degreeRadio + "',a.raters='" + userAccount + "',a.expert_comment1='" + expertComment1 + "',a.expert_comment2='" + expertComment2 + "',a.hee_status=2,a.fen_status=1 where num='" + num + "' and reservations_id='" + reservationsId + "'";
        return sqlMapper.update(updateSql);
    }

    public int subCoreB(String reservationsId, String num, String b1, String b2, String b3, String b4, String b5) {
        String updateSql = "update root_orthodontics_info a set a.B1='" + b1 + "',a.B2='" + b2 + "',a.B3='" + b3 + "',a.B4='" + b4 + "',a.B5='" + b5 + "' where num='" + num + "' and reservations_id='" + reservationsId + "'";
        return sqlMapper.update(updateSql);
    }

    public int subOrthodonticsd(String reservationsId, String num, String doctor, String nurse, String molarTooth, String canineTooth, String overlap, String medicalRecordNumberValue, String userAccount) {
        String updateSql = "update root_orthodontics_info a set a.doctor1='" + doctor + "',a.status=3,a.submission_state2 = 1 ,a.nurse1='" + nurse + "',a.create_time='" + DateTimeKit.now() + "',a.medical_record_number='" + medicalRecordNumberValue + "',a.molar_tooth='" + molarTooth + "',a.canine_tooth='" + canineTooth + "',a.overlap='" + overlap + "' " +
                " where num='" + num + "' and reservations_id='" + reservationsId + "' and create_name='" + userAccount + "'";
        String upimgs = "update root_orthodontics_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                " and create_name='" + userAccount + "';";
        sqlMapper.update(upimgs);
        return sqlMapper.update(updateSql);
    }
    public int deleteOrthodonticScore(String ids) {
        String num = ids.split(",")[0];
        String reservationsId = ids.split(",")[1];
        String selsql = "delete from root_orthodontics_info where num='" + num + "' and reservations_id='" + reservationsId + "' and status=0;";
        String selsql1 = "delete from root_orthodontics_imgs_info where num='" + num + "' and reservations_id='" + reservationsId + "' and classification=1 ;";
        sqlMapper.delete(selsql1);
        return sqlMapper.delete(selsql);
    }
    public int deleteOrthodonticScore1(String ids) {
        String num = ids.split(",")[0];
        String reservationsId = ids.split(",")[1];
        String selsql = "update root_orthodontics_info a set a.status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' and status=2;";
        String selsql1 = "delete from root_orthodontics_imgs_info where num='" + num + "' and reservations_id='" + reservationsId + "' and classification=2 ;";
        sqlMapper.delete(selsql1);
        return sqlMapper.update(selsql);
    }
    public int storagePlant(String reservationsId, String num, String customerName, String doctor, String nurse, String userAccount, String outpatientName) {
        String selsql = "select id from root_plant_info where num='" + num + "' limit 1;";
        DaulInfo daulInfo = sqlMapper.selectOne(selsql, DaulInfo.class);
        if (daulInfo == null) {
            String insertSql = "INSERT INTO root_plant_info (`reservations_id`,`num`,`create_name`,create_time,arrival_store,doctor,nurse,customer_name) VALUES " +
                    "('" + reservationsId + "','" + num + "','" + userAccount + "','" + DateTimeKit.now() + "','" + outpatientName + "','" + doctor + "','" + nurse + "','" + customerName + "');";
            sqlMapper.insert(insertSql);
        }
        String upimgs = "update root_plant_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                " and create_name='" + userAccount + "';";
        return sqlMapper.update(upimgs);
    }

    public int subPlant(String reservationsId, String num, String doctor1, String nurse1, String customerName, String userAccount, String outpatientName) {
        String selsql = "select id from root_plant_info where num='" + num + "' limit 1;";
        DaulInfo daulInfo = sqlMapper.selectOne(selsql, DaulInfo.class);
        if (daulInfo == null) {
            String insertSql = "INSERT INTO root_plant_info (`reservations_id`,`num`,`create_name`,create_time,`status`,arrival_store,doctor,nurse,customer_name) VALUES " +
                    "('" + reservationsId + "','" + num + "','" + userAccount + "','" + DateTimeKit.now() + "',1,'" + outpatientName + "','" + doctor1 + "','" + nurse1 + "','" + customerName + "');";
            String upimgs = "update root_plant_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" +userAccount + "';";
            sqlMapper.update(upimgs);
            return sqlMapper.insert(insertSql);
        } else {
            String updateSql = "update root_plant_info set status=1,doctor1 = '"+doctor1+"',nurse1 = '"+nurse1+"',create_time='" + DateTimeKit.now() + "' where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" +userAccount + "';";
            String upimgs = "update root_plant_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount+ "';";
            sqlMapper.update(upimgs);
            return sqlMapper.update(updateSql);
        }
    }

    public int deleteSubPlant(String ids) {
        String num = ids.split(",")[0];
        String reservationsId = ids.split(",")[1];
        String selsql = "delete from root_plant_info where num='" + num + "' and reservations_id='" + reservationsId + "' ;";
        String selsql1 = "delete from root_plant_imgs_info where num='" + num + "' and reservations_id='" + reservationsId + "' ;";
        sqlMapper.delete(selsql1);
        return sqlMapper.delete(selsql);
    }

    public int storageRootCanal(String reservationsId, String num, String treatmentTypes, String consumptionAmount, String toothPositionA, String toothPositionB, String toothPositionC, String toothPositionD, String toothPosition, String doctor, String nurse, String customerName, String userAccount, String outpatientName) {
        String selsql = "select id from root_canal_info where num='" + num + "' limit 1;";
        DaulInfo daulInfo = sqlMapper.selectOne(selsql, DaulInfo.class);
        if (daulInfo == null) {
            String insertSql = "INSERT INTO root_canal_info (`reservations_id`,`num`, `treatment_types`, `consumption_amount`, " +
                    "`toothPositionA`, `toothPositionB`, `toothPositionC`, `toothPositionD`,`toothPosition`, `create_name`,arrival_store,doctor,nurse,customer_name) VALUES " +
                    "('" + reservationsId + "','" + num + "','" + treatmentTypes + "','" + consumptionAmount + "','" + toothPositionA + "','" + toothPositionB + "'," +
                    "'" + toothPositionC + "','" + toothPositionD + "','" + toothPosition + "','" + userAccount + "','" + outpatientName + "','" + doctor + "','" + nurse + "','" + customerName + "');";
            sqlMapper.insert(insertSql);
        }
        String upimgs = "update root_canal_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                " and create_name='" + userAccount + "';";
        return sqlMapper.update(upimgs);
    }

    public int subRootCanal(String reservationsId, String num, String treatmentTypes, String consumptionAmount, String toothPositionA, String doctor, String nurse, String customerName, String toothPositionB, String toothPositionC, String toothPositionD, String toothPosition, String userAccount, String outpatientName) {
        String selsql = "select id from root_canal_info where num='" + num + "' limit 1;";
        DaulInfo daulInfo = sqlMapper.selectOne(selsql, DaulInfo.class);
        if (daulInfo == null) {
            String insertSql = "INSERT INTO root_canal_info (`reservations_id`,`num`, `treatment_types`, `consumption_amount`, " +
                    "`toothPositionA`, `toothPositionB`, `toothPositionC`, `toothPositionD`,`toothPosition`, `create_name`,`status`,arrival_store,doctor,nurse,customer_name) VALUES " +
                    "('" + reservationsId + "','" + num + "','" + treatmentTypes + "','" + consumptionAmount + "','" + toothPositionA + "','" + toothPositionB + "'," +
                    "'" + toothPositionC + "','" + toothPositionD + "','" + toothPosition + "'," +
                    "'" + userAccount + "',1,'" + outpatientName + "','" + doctor + "','" + nurse + "','" + customerName + "');";
            String upimgs = "update root_canal_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            sqlMapper.update(upimgs);
            return sqlMapper.insert(insertSql);
        } else {
            String updateSql = "update root_canal_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            String upimgs = "update root_canal_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            sqlMapper.update(upimgs);
            return sqlMapper.update(updateSql);
        }
    }

    public int deleteRootCanal(String ids) {
        String num = ids.split(",")[0];
        String reservationsId = ids.split(",")[1];
        String selsql = "delete from root_canal_info where num='" + num + "' and reservations_id='" + reservationsId + "' ;";
        String selsql1 = "delete from root_canal_imgs_info where num='" + num + "' and reservations_id='" + reservationsId + "' ;";
        sqlMapper.delete(selsql1);
        return sqlMapper.delete(selsql);
    }

    public int storagePeriodontal(String reservationsId, String num, String customerName, String doctor, String nurse, String userAccount, String outpatientName) {
        String selsql = "select id from root_periodontal_info where num='" + num + "' limit 1;";
        DaulInfo daulInfo = sqlMapper.selectOne(selsql, DaulInfo.class);
        if (daulInfo == null) {
            String insertSql = "INSERT INTO root_periodontal_info (`reservations_id`,`num`,`create_name`,arrival_store,doctor,nurse,customer_name) VALUES " +
                    "('" + reservationsId + "','" + num + "','" + userAccount + "','" + outpatientName + "','" + doctor + "','" + nurse + "','" + customerName + "');";
            sqlMapper.insert(insertSql);
        }
        String upimgs = "update root_periodontal_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                " and create_name='" + userAccount + "';";
        String upSql = "update root_periodontal_info a set a.doctor='" + doctor + "',a.nurse='" + nurse + "' where a.num='" + num + "' and a.reservations_id='" + reservationsId + "' " +
                " and a.create_name='" + userAccount + "';";
        sqlMapper.update(upSql);
        return sqlMapper.update(upimgs);
    }

    public int subPeriodontal(String reservationsId, String num, String doctor, String nurse, String customerName, String userAccount, String outpatientName) {
        String selsql = "select id from root_periodontal_info where num='" + num + "' limit 1;";
        DaulInfo daulInfo = sqlMapper.selectOne(selsql, DaulInfo.class);
        if (daulInfo == null) {
            String insertSql = "INSERT INTO root_periodontal_info (`reservations_id`,`num`,`create_name`,`status`,arrival_store,doctor,nurse,customer_name) VALUES " +
                    "('" + reservationsId + "','" + num + "','" + userAccount + "',1,'" + outpatientName + "','" + doctor + "','" + nurse + "','" + customerName + "');";
            String upimgs = "update root_periodontal_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            sqlMapper.update(upimgs);
            return sqlMapper.insert(insertSql);
        } else {
            String updateSql = "update root_periodontal_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            String upimgs = "update root_periodontal_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            sqlMapper.update(upimgs);
            return sqlMapper.update(updateSql);
        }
    }

    public int deleteStoragePeriodontal(String ids) {
        String num = ids.split(",")[0];
        String reservationsId = ids.split(",")[1];
        String selsql = "delete from root_periodontal_info where num='" + num + "' and reservations_id='" + reservationsId + "' ;";
        String selsql1 = "delete from root_periodontal_imgs_info where num='" + num + "' and reservations_id='" + reservationsId + "' ;";
        sqlMapper.delete(selsql1);
        return sqlMapper.delete(selsql);
    }
    public int storageScaling(String reservationsId, String num, String customerName, String doctor, String nurse, String userAccount, String outpatientName) {
        String selsql = "select id from root_scaling_info where num='" + num + "' limit 1;";
        DaulInfo daulInfo = sqlMapper.selectOne(selsql, DaulInfo.class);
        if (daulInfo == null) {
            String insertSql = "INSERT INTO root_scaling_info (`reservations_id`,`num`,`create_name`,arrival_store,doctor,nurse,customer_name) VALUES " +
                    "('" + reservationsId + "','" + num + "','" + userAccount + "','" + outpatientName + "','" + doctor + "','" + nurse + "','" + customerName + "');";
            sqlMapper.insert(insertSql);
        }
        String upimgs = "update root_scaling_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                " and create_name='" + userAccount + "';";
        return sqlMapper.update(upimgs);
    }

    public int subScaling(String reservationsId, String num, String doctor, String nurse, String customerName, String userAccount, String outpatientName) {
        String selsql = "select id from root_scaling_info where num='" + num + "' limit 1;";
        DaulInfo daulInfo = sqlMapper.selectOne(selsql, DaulInfo.class);
        if (daulInfo == null) {
            String insertSql = "INSERT INTO root_scaling_info (`reservations_id`,`num`,`create_name`,`status`,arrival_store,doctor,nurse,customer_name) VALUES " +
                    "('" + reservationsId + "','" + num + "','" + userAccount + "',1,'" + outpatientName + "','" + doctor + "','" + nurse + "','" + customerName + "');";
            String upimgs = "update root_scaling_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            sqlMapper.update(upimgs);
            return sqlMapper.insert(insertSql);
        } else {
            String updateSql = "update root_scaling_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            String upimgs = "update root_scaling_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            sqlMapper.update(upimgs);
            return sqlMapper.update(updateSql);
        }
    }

    public int deleteSubScaling(String ids) {
        String num = ids.split(",")[0];
        String reservationsId = ids.split(",")[1];
        String selsql = "delete from root_scaling_info where num='" + num + "' and reservations_id='" + reservationsId + "' ;";
        String selsql1 = "delete from root_scaling_imgs_info where num='" + num + "' and reservations_id='" + reservationsId + "' ;";
        sqlMapper.delete(selsql1);
        return sqlMapper.delete(selsql);
    }

    public int storageRepair(String reservationsId, String num, String customerName, String doctor, String nurse, String userAccount, String outpatientName) {
        String selsql = "select id from root_repair_info where num='" + num + "' limit 1;";
        DaulInfo daulInfo = sqlMapper.selectOne(selsql, DaulInfo.class);
        if (daulInfo == null) {
            String insertSql = "INSERT INTO root_repair_info (`reservations_id`,`num`,`create_name`,arrival_store,doctor,nurse,customer_name) VALUES " +
                    "('" + reservationsId + "','" + num + "','" + userAccount + "','" + outpatientName + "','" + doctor + "','" + nurse + "','" + customerName + "');";
            sqlMapper.insert(insertSql);
        }
        String upimgs = "update root_repair_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                " and create_name='" + userAccount + "';";
        return sqlMapper.update(upimgs);
    }

    public int subRepair(String reservationsId, String num, String doctor, String nurse, String customerName, String userAccount, String outpatientName) {
        String selsql = "select id from root_repair_info where num='" + num + "' limit 1;";
        DaulInfo daulInfo = sqlMapper.selectOne(selsql, DaulInfo.class);
        if (daulInfo == null) {
            String insertSql = "INSERT INTO root_repair_info (`reservations_id`,`num`,`create_name`,`status`,arrival_store,doctor,nurse,customer_name) VALUES " +
                    "('" + reservationsId + "','" + num + "','" + userAccount + "',1,'" + outpatientName + "','" + doctor + "','" + nurse + "','" + customerName + "');";
            String upimgs = "update root_repair_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            sqlMapper.update(upimgs);
            return sqlMapper.insert(insertSql);
        } else {
            String updateSql = "update root_repair_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            String upimgs = "update root_repair_imgs_info set status=1 where num='" + num + "' and reservations_id='" + reservationsId + "' " +
                    " and create_name='" + userAccount + "';";
            sqlMapper.update(upimgs);
            return sqlMapper.update(updateSql);
        }
    }

    public int subRepair(String id) {
        String sql = "select * from root_orthodontics_info where id='"+id+"'";
        RootImageInfo info = sqlMapper.selectOne(sql,RootImageInfo.class);
        if("1".equals(info.getStatus())){
            String updateSql ="update root_orthodontics_info a set a.status=0,a.submission_state=0 where id='"+id+"' ";
            sqlMapper.update(updateSql,RootImageInfo.class);
        }
        if("3".equals(info.getStatus())) {
            String updateSql = "update root_orthodontics_info a set a.status=2,a.submission_state2=0 where id='" + id + "' ";
            sqlMapper.update(updateSql, RootImageInfo.class);
        }
        return 1;
    }
    public int deleteSubRepair(String ids) {
        String num = ids.split(",")[0];
        String reservationsId = ids.split(",")[1];
        String selsql = "delete from root_repair_info where num='" + num + "' and reservations_id='" + reservationsId + "' ;";
        String selsql1 = "delete from root_repair_imgs_info where num='" + num + "' and reservations_id='" + reservationsId + "' ;";
        sqlMapper.delete(selsql1);
        return sqlMapper.delete(selsql);
    }
    public int deleteRootCanalImgs(String id) {
        String delsql = "delete from root_canal_imgs_info where imgid='" + id + "'";
        return sqlMapper.delete(delsql);
    }
    public int deleteScalingImgs(String id) {
        String delsql = "delete from root_scaling_imgs_info where imgid='" + id + "'";
        return sqlMapper.delete(delsql);
    }

    public int deletePeriodontalImgs(String id) {
        String delsql = "delete from root_periodontal_imgs_info where imgid='" + id + "'";
        return sqlMapper.delete(delsql);
    }

    public int deleteOrthodontics(String id) {
        String delsql = "delete from root_orthodontics_imgs_info where imgid='" + id + "'";
        return sqlMapper.delete(delsql);
    }
    public int deletePlantImgs(String id) {
        String delsql = "delete from root_plant_imgs_info where imgid='" + id + "'";
        return sqlMapper.delete(delsql);
    }
    public int deleteRepairImgs(String id) {
        String delsql = "delete from root_repair_imgs_info where imgid='" + id + "'";
        return sqlMapper.delete(delsql);
    }
    public int addDiagnose(String projectCategory, String projectName, String userAccount) {
        String insertSql = "insert into diagnose_project_info (add_name,project_category,project_name,update_time) values ('" + userAccount + "','" + projectCategory + "','" + projectName + "','" + DateTimeKit.now() + "')";
        return sqlMapper.insert(insertSql);
    }

    public int updateDiagnose(String id, String projectCategory, String projectName, String userAccount) {
        String updateSql = "update diagnose_project_info a set a.add_name='" + userAccount + "',a.project_category='" + projectCategory + "',a.project_name='" + projectName + "',a.update_time='" + DateTimeKit.now() + "' where id='" + id + "'";
        return sqlMapper.update(updateSql);
    }
    public int deleteDiagnose(String id) {
        String deleteSql = "delete from diagnose_project_info where id='" + id + "'";
        return sqlMapper.delete(deleteSql);
    }
    public List<QualityAssuranceInfo> getList(String reservationsId) {
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select t1.id as id,t1.outpatient_name as outpatientName,t1.add_name as addName ,t1.add_time as addTime,t1.status as status, " +
                "        t1.customer_name as customerName,t1.customer_phone as customerPhone,t1.num as num,t1.reservations_id as reservationsId, " +
                "        t1.tooth_position as toothPosition,t1.score as score,t1.image_url as imageUrl,t1.doctor as doctor,t3.create_time as createTime, " +
                "        t3.A1 as A1,t3.A2 as A2,t3.A3 as A3,t3.A4 as A4,t3.A5 as A5,t3.A6 as A6,t3.A7 as A7," +
                "        t3.B1 as B1,t3.B2 as B2,t3.B3 as B3,t3.B4 as B4,t3.B5 as B5,t3.B6 as B6,t3.B7 as B7,t1.icon_name as iconName, " +
                "        (SELECT score from inco_info_standard limit 1 ) as standardScore,t3.photo_type as photoType, " +
                "        (select sum( " +
                "        g.A1 / 3 + g.A2 / 3 + g.A3 / 3 + " +
                "        IF (g.photo_type IN(1, 3), 0, g.A4) + " +
                "        IF (g.photo_type IN(1, 2), 0, g.A5) + " +
                "        IF (g.photo_type IN(1, 2), 0, g.A6) + " +
                "        IF (g.photo_type IN(1, 2), 0, g.A7) " +
                "        ) from (SELECT " +
                "        d.photo_type as photo_type,d.A1,d.A2,d.A3,d.A4,d.A5,d.A6,d.A7,e.num " +
                "        FROM icon_infos e LEFT JOIN intelligent_score_details_info2 d ON e.id = d.id2 GROUP BY e.id ) g WHERE  g.num = t1.num) AS scoreTOTAL " +
                "        from icon_infos t1 left JOIN intelligent_score_details_info2 t3 on t1.id= t3.id2 " +
                "        where 1=1 and t1.reservations_id='" + reservationsId + "'" +
                "        group by t1.id " +
                "        order by t1.add_time desc,t1.icon_name='术后',t1.icon_name='术中',t1.icon_name='术前'";
        List<QualityAssuranceInfo> qualityAssuranceInfoList = sqlMapper.selectList(sql, QualityAssuranceInfo.class);
        return qualityAssuranceInfoList;
    }


}